InputBox Function to Edit Macro

Question

Windows("XXX Consolidated PL 03312018.xlsx").Activate
ActiveCell.Offset(187, 8).Range("A1").Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select

How do I create a macro or InputBox function to where I can change the numbers "03312018"?

The numbers represent a date. Every month the spread sheet changes so I want to type the new numbers in manually.

A recommendation, not related to your question - the last 3 lines are almost certainly unnecessary. See [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jun 26 '18 at 18:48

score 1 · Accepted Answer · answered Jun 26 '18 at 18:37

1

Something like this will work. Although you may want to validate the entry before proceeding with your code:

Dim MyDate as Variant
MyDate = Inputbox ("Input Date Serial")

Windows("XXX Consolidated PL " & MyDate & ".xlsx").Activate

answered Jun 26 '18 at 18:37

urdearboy

14,439
5
28
58

You're amazing. Thank you soooo much. I was trying to figure it out for hours. – H. Nguyen Jun 26 '18 at 18:59
Please see QHarr's solution. I would address this, but it is already addressed here. The workbook was not found so either the wrong # was inputted, the workbook was not open, or the file string name has an inconsistency. – urdearboy Jun 26 '18 at 19:07
I opened the new workbook and it worked. Apologies -- I assumed it just changed the numbers. Thank you once again. – H. Nguyen Jun 26 '18 at 19:12

score 1 · Answer 2 · answered Jun 26 '18 at 18:42

1

Something like:

Option Explicit 
Public Sub test()
    Dim wkbk As String, dateVar As String
    'dateVar = "03312018"
    dateVar = Application.InputBox("Enter date string")
    If dateVar = vbNullString Then Exit Sub
    wkbk = "XXX Consolidated PL " & dateVar & ".xlsx"
    On Error GoTo Errhand
    Windows(wkbk).Activate
    'other code
    Exit Sub
Errhand:
    If Err.Number <> 0 Then
        Select Case Err.Number
        Case 9
            MsgBox "Workbook not found"
            'Other handling
        End Select
    End If
End Sub

answered Jun 26 '18 at 18:42

QHarr

83,427
12
54
101

No input box pops up once I paste the code into a Command Button – H. Nguyen Jun 26 '18 at 19:08
Does the command button call the sub test? Which you would rename btw. – QHarr Jun 26 '18 at 19:11
1

Ambiguous name detected. Thank you though -- urdearboy's solution helped – H. Nguyen Jun 26 '18 at 19:13
1

Then you have something else called Test present. You simply need to change the name to something unique. Public Sub GetDateString() – QHarr Jun 26 '18 at 19:14

InputBox Function to Edit Macro

2 Answers2