Make Text to Numbers Code More User Friendly

[mrmmickle1]

I would like to make the below code more flexible. Can someone show me a way that I can have the user input the column in a simple input box?

So instead of having to go into the VB editor a novice could change the reference easily...



Sub FormatTextAsNumbers()
Range("Q:Q").Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = "General"
.Value = .Value
Columns("Q:Q").Select 'Changes Selection to two decimal format
Selection.NumberFormat = "0.00"
End With
End Sub

[Rick Rothstein]

Try using this macro instead of the one you posted...

Sub FormatTextAsNumbers()
Dim Col As Range
Set Col = Application.InputBox("Please select a column...", Type:=8)
Set Col = Columns(Col.Cells(1).Column)
Col.NumberFormat = "General"
Col.NumberFormat = "0.00"
End Sub

[mrmmickle1]

Sub FormatTextAsNumbers()
Dim Col As Range
Set Col = Application.InputBox("Please select a column...", Type:=8)
Set Col = Columns(Col.Cells(1).Column)
Col.NumberFormat = "General"
Col.NumberFormat = "0.00"
End Sub


Rick,

The Input box works great now however, the little green tick marks that signify text is stored as a number do not seem to disappear when I test the code. Am I doing somthing wrong? I am using the code in conjunction with this other code but i do not think it would effect the result... :



Sub MacroShortcutList()
'Standard Module code!
'Display pick list box.
Dim Message, Title, Default, MyPick

'Set prompt.
'Add other macros to list with & Chr(13) & "Macro Name"
Message = "Select the number of the macro you want to run:" & Chr(13) & _
"1. Select No Macro" & Chr(13) & "2. Run Cost Analysis at 50%" & Chr(13) & "3. Customer Service Report" & Chr(13) & "4. Delete Rows Based On Criteria" _
& Chr(13) & "5. Insert Piedmont Logo" & Chr(13) & "6. Run Flat Rate Cost Analysis" & Chr(13) & "7. Format Text to Numbers"


'Set title.
Title = "Select a Macro to run!"
'Set default.
Default = "1"

'Display message, title, and default value.
MyPick = InputBox(Message, Title, Default)
'Optional, control box position!
'Display dialog box at position 100, 100.
'MyPick = InputBox(Message, Title, Default, 100, 100)

Select Case MyPick

Case 1
MsgBox "No Macro Selected!"

'Add additional Case code as needed!
Case 2
Call RunCostAnalysisFifty

Case 3
Call CustomerServiceReport

Case 4

Call DeleteRowsCriteria

Case 5
Call PiedmontLogo

Case 6
Call RunCostAnalysisFlatRate

Case 7
Call FormatTextAsNumbers

Case Else
Exit Sub

End Select
End Sub

[mrmmickle1]

Rick,

I always appreciate you taking the time to help me with code. I just tried the above code you provided again. I was certain that I must have missed something but, it still seems unsuccessful at changing numbers stored as text to two decimal number format. Is there something I am missing? I am supposed to enter columns in this format correct? $C:$C...

Thanks for any insight.

[Rick Rothstein]

Rick,

I always appreciate you taking the time to help me with code. I just tried the above code you provided again. I was certain that I must have missed something but, it still seems unsuccessful at changing numbers stored as text to two decimal number format. Is there something I am missing? I am supposed to enter columns in this format correct? $C:$C...

Can you post a copy of your workbook with the unprocessed data in it so I can try the code out on your actual data in order to see what is happening (or not happening)?

[mrmmickle1]

Rick,

Of course. Here is an example of what I am having trouble getting to change. The green tick marks in column C, as well as the formatting seem not to be changing as expected. Thanks again for the help.

-Matt

[Rick Rothstein]

Rick,

Of course. Here is an example of what I am having trouble getting to change. The green tick marks in column C, as well as the formatting seem not to be changing as expected. Thanks again for the help.
It looks like this code may be working correctly...

Sub FormatTextAsNumbers()
Dim Col As Range
Set Col = Application.InputBox("Please select a column...", Type:=8)
Set Col = Columns(Col.Cells(1).Column)
Col.NumberFormat = "General"
Col.NumberFormat = "0.00"
Col.Value = Col.Value
End Sub

[mrmmickle1]

Rick,

You are incredible. THanks so much for your efforts. I sampled the code and it of course works perfectly for my purposes. I was wondering... I have no purpose to speed this macro up but, just for good practice. If I wanted the same end result is it feasible to delete this portion of code?
Col.NumberFormat = "General"

[snb]

or

Sub M_snb_002()
on error resume next
Application.InputBox("Please select a column...", Type:=8).EntireColumn.NumberFormat = "0.00"
End Sub

[Rick Rothstein]

Rick,

You are incredible. THanks so much for your efforts. I sampled the code and it of course works perfectly for my purposes. I was wondering... I have no purpose to speed this macro up but, just for good practice. If I wanted the same end result is it feasible to delete this portion of code?
Col.NumberFormat = "General"
Yes, you can remove that line... it is left over from a previous test. You might want to consider using snb's code instead, though... besides being more compact, it also includes an error handler as well (although I like to turn the error handler off afterwards by putting an

On Error GoTo 0

statement at the end).

[mrmmickle1]

Snb/Rick,

Thank you both. I appreciate your time. The final product I am using looks like this:



Sub FormatTexttoNumbers()
Dim Col As Range
On Error Resume Next
Set Col = Application.InputBox("Please select a column...", Type:=8)
Set Col = Columns(Col.Cells(1).Column)
Col.NumberFormat = "0.00"
Col.Value = Col.Value
On Error GoTo 0
End Sub


I tried to use Snb's code for a shorter solution but it didn't seem to change the format, it seemed to be the same problem as before. The formatting wasn't changing. Does this look like a good solution? I am unfamiliar with error handling. I have only used it in one macro I have written. Is it considered proper to put the error statement below the Dims or should it come before all statements at the beginning of the macro?