CODEWITHSUNDEEP
excelvba
Matt M
Matt M

Reputation: 11

Using VBA Input Box answer as a Variable in code

I have a table of data (thousands of rows and mostly numeric values) that I want to change the formatting for depending who the end user is. e.g. sometimes to display as whole numbers, sometimes to display in millions with a single decimal place. I have written code that loops through the range of cells and applies a certain format type assigned at the time of running the macro. I have nine format types that can be used and for ease I have assigned the syntax or text related to each format type to a variable. e.g.

When I run this procedure I simply access the code in the VB editor and type in the variable related to the format type I want, e.g. by typing in "F7" as the variable the format type applied to my data will be millions with no decimals. When I type in the variable code (e.g. F7) the macro works fine.

To allow other staff to use this macro, I have added an input box which asks for the format type to be entered e.g. F3 or F7 etc... I then (attempt to) use the result from the Input Box in my code. The intention is that the code would recognize F7 as a pre-defined variable and would assign the defined formatting code. However, the result is that my code does not recognize the value from the input box as a variable, rather is assumes the result is actually a format type and creates a custom format type of F7, which of course makes no sense and my cell values in the table show as "F7" in every cell even though the underlying number still exist in each cell.

Why does my input box approach not work, secondly is there a better way to have the user pick which format type they want without having to enter the VBA code?

My issue is at the very bottom of the code, if I use option 1 below the macro runs perfectly, if I use option 2 which uses the inputbox method, then the problem occurs:

  1. Selection.NumberFormat = F7 (THIS WORKS GREAT)
  2. Selection.NumberFormat = var_FormatCode (THIS WON'T WORK!)

below is a copy of the relevant code:

Sub xx3()
' CHANGE P&L ROW FORMATS - INCLUDE USER INPUT BOXES

'Declare Variables
Dim x As Integer 'number of rows this macro applies to

Dim F1 As String
Dim F2 As String
Dim F3 As String
Dim F4 As String
Dim F5 As String
Dim F6 As String
Dim F7 As String
Dim F8 As String
Dim F9 As String

'Define/Set variables for "Format Types"
F1 = "#,##0_);[Red](#,##0);-"
F2 = "#,##0.0_);[Red](#,##0.0);-"
F3 = "#,##0.00_);[Red](#,##0.00);-"
F4 = "#,##0,_);[Red](#,##0,);-"
F5 = "#,##0.0,_);[Red](#,##0.0,);-"
F6 = "#,##0.00,_);[Red](#,##0.00,);-"
F7 = "#,##0,,_);[Red](#,##0,,);-"
F8 = "#,##0.0,,_);[Red](#,##0.0,,);-"
F9 = "#,##0.00,,_);[Red](#,##0.00,,);-"

'Declare variables for "Format Type" InputBox
Dim Prompt2 As String
Dim Caption2 As String
Dim DefaultValue2 As String
Dim var_FormatCode As String

'Define/Set variables for "Format Type" InputBox
Prompt2 = "Enter **Format Type Code** to use....."
Caption2 = "User Data Request - Input Box....."
DefaultValue2 = none

'Prompt User for Row Type & Format Code
var_FormatCode = InputBox(Prompt2, Caption2, DefaultValue2)

For x = 1 To 1000 'Number of rows this macro covers

    If Cells(x, 5) = 1 Then ' macro only applies formats to rows with a "1"
        Range(Cells(x, 16), Cells(x, 100)).Select
        Selection.NumberFormat = var_FormatCode
    End If
Next x

Range("A1").Select

End Sub

Below is how table displays when I use inputBox

enter image description here

Upvotes: 1

Views: 2263

Answers (1)

user4039065
user4039065

Reputation:

You cannot name a variable from the string returned by an InputBox. You might create an array of the strings and use the right-side number to determine the array element to return into the .NumberFormat.

DefaultValue2 = none should be DefaultValue2 = "none" or DefaultValue2 = vbnullstring. none without quotes has no context in what you are attempting.

Dim F As variant

'Define/Set variables for "Format Types"
F = array("#,##0_);[Red](#,##0);-", "#,##0.0_);[Red](#,##0.0);-", "#,##0.00_);[Red](#,##0.00);-", _
          "#,##0,_);[Red](#,##0,);-", "#,##0.0,_);[Red](#,##0.0,);-", "#,##0.00,_);[Red](#,##0.00,);-", _
          "#,##0,,_);[Red](#,##0,,);-", "#,##0.0,,_);[Red](#,##0.0,,);-", "#,##0.00,,_);[Red](#,##0.00,,);-")

...
Dim DefaultValue2 As String
Dim var_FormatCode As String

...
DefaultValue2 = "none"

var_FormatCode = InputBox(Prompt2, Caption2, DefaultValue2)

if isnumeric(right(var_FormatCode, 1)) then
    'the array is zero-based; i.e. the first element is F(0)
    If Cells(x, 5) = 1 Then ' macro only applies formats to rows with a "1"
        Range(Cells(x, 16), Cells(x, 100)).NumberFormat = F(cint(right(var_FormatCode, 1))-1)
    end if
end if

Upvotes: 3

Related Questions