Make Text to Numbers Code More User Friendly

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...

Code:

---

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

---

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

Code:

---

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

---

Code:

---

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... :

Code:

---

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

---

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.

Quote:

---

Originally Posted by 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...

---

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)?

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

Quote:

---

Originally Posted by 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.

---

It looks like this code may be working correctly...

Code:

---

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

---

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?

Code:

---

  Col.NumberFormat = "General"

---

or

Code:

---

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

---

Quote:

---

Originally Posted by 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?

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

Code:

---

On Error GoTo 0

---

statement at the end).

Snb/Rick,

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

Code:

---

  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?