title: Using VBA Input Box answer as a Variable in code
body: <p>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.  </p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>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:</p>
  
  <ol>
  <li><code>Selection.NumberFormat = F7</code>    (THIS WORKS GREAT)</li>
  <li><code>Selection.NumberFormat = var_FormatCode</code>     (THIS WON'T WORK!)</li>
  </ol>
  
  <p>below is a copy of the relevant code:</p>
  
  <pre><code>Sub xx3()
  ' CHANGE P&amp;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 &amp; 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
  </code></pre>
  
  <p>Below is how table displays when I use inputBox</p>
  
  <p><a href="https://i.stack.imgur.com/hRLaa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hRLaa.png" alt="enter image description here"></a></p>