Thread: Enter user parameter into a cell

Hi all.

I have an excel 2007 file whose datasource is a SQL table. When the user opens the template it automatically prompts them to enter MM/YYYY and a second MM/YYYY

I want to be able to get both those values and insert them into the top row so they know what ranges they entered.

I don't want the parameters to be taken from specific fields but rather how it's currently done (asks for both parameters when the file is opened) because there is a macro that does quite a bit of cutting and pasting and formatting so adding 2 fields for parameters would meaning changing the macro.

I tried looking at the command text to see if I can reference the parameters but the parameters appear as "?"

Any ideas how I can do this?

Thank you in advance.

Dim r As Long

Range("B:B").Insert Shift:=xlToRight

 Range("K:K").Select
    Selection.Cut
    Range("B:B").Select
  
    ActiveSheet.Paste

    Application.CutCopyMode = False

Range("B2") = "MAXQty"

Columns("K:K").Select
Selection.Delete

'==========================================================


Range("D:D").Insert Shift:=xlToRight
Range("D2") = "MAXQtyCASE"
With Worksheets("Sheet1")
    r = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("D3:D" & r).Formula = "=RoundUp(B3/C3, 0)"

End With

'==============================================================================================

Range("J:J").Insert Shift:=xlToRight
Range("J2") = "Base Qty"

With Worksheets("Sheet1")
    r = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("J3:J" & r).Formula = "=(ROUNDDOWN(((I3-1)/F3),0))*(ROUNDDOWN(((H3-1)/E3),0))"
    
End With

'==============================================================================================

Range("K:K").Insert Shift:=xlToRight
Range("K2") = "CA Qty"

With Worksheets("Sheet1")
    r = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("K3:K" & r).Formula = "=(ROUNDDOWN(((I3-1)/F3),0))*(ROUNDDOWN(((H3-1)/E3),0))"
    
End With

'==============================================================================================

Range("L:L").Insert Shift:=xlToRight
Range("L2") = "Average TGF"

With Worksheets("Sheet1")
    r = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("L3:L" & r).Formula = "=(ROUNDUP(D3/J3,0))*G3"
    
End With

'==============================================================================================

Range("M:M").Insert Shift:=xlToRight
Range("M2") = "Average MUO"

With Worksheets("Sheet1")
    r = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("M3:M" & r).Formula = "=(ROUNDUP(D3/K3,0))*G3"
    
End With

Range("R2") = "VTA"

With Worksheets("Sheet1")
r = .Range("B" & Rows.Count).End(xlUp).Row
Range("A3:R" & r).Sort Key1:=Columns("R"), Order1:=xlDescending, Header:=xlYes
End With




Range("B1:R1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(""VTA REPORT       "", TEXT(TODAY(),""mm/dd/yyyy""))"
    Range("B1:R1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.599963377788629
        .PatternTintAndShade = 0
    End With