Avatar of RWayneH
RWayneH
Flag for United States of America asked on

Increment default InPutBox value

I have an inputbox that has a default value of today date and I would like to start incrementing it instead having it the same each time.  There is a small issue that when I use my Sub Save_XML it is called from within another Sub that is running a Do While loop.  If the InputBox is going to increment now, I will need to pass a variable from one sub to the other in order to keep a counter correct.  Ideally I would like to increment the default text in the InputBox to "File001", "File002"  ....

So two questions in one here.  One how do I make a variable global so it can be used in a called sub from a sub?  Two how would I change the code below to use that variable to increment the InputBox default value?

Please advise and thanks.

Sub Save_XML()
    Dim strFilePath, strFileName As String
    
    strFilePath = "C:\_SAP\LynxFiles\" 'This needs to be changed to correct directory and must end with \
    'strFilePath = gstDestinationFolder
    
    strFileName = InputBox("Please enter a filename or click OK to accept the default.", "Save File", _
                "TestFile-" & Format(Now(), "yyyy-mm-dd")) '<--- change to new default

    If strFileName = "" Then
        Sheets("SIF Data").Select
        ActiveWindow.SelectedSheets.Visible = False
        Sheets("Order Line Items").Select
        ActiveWindow.SelectedSheets.Visible = False
    
        Exit Sub 'Will happen if Cancel is pressed
    End If
    strFileName = strFileName & ".xml"
    
    Application.DisplayAlerts = False
    ActiveSheet.Copy
    
    ActiveWorkbook.SaveAs Filename:= _
        strFilePath & strFileName, FileFormat:=xlTextPrinter, CreateBackup:=False
    ActiveWorkbook.Close savechanges:=False
    
    Application.DisplayAlerts = True

     Sheets("MasterCopy").Select
    
End Sub

Open in new window

Microsoft ExcelVBAMicrosoft Office

Avatar of undefined
Last Comment
RWayneH

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
RWayneH

ASKER
Ok... while debugging..  I have a Sub with a do while, that calls another sub, that has the call to the code above...  probably not the best structure, but when it gets the InputBox, the values are Empty...  Is there a way to make a variable truly global so it can be used across a Sub that calls a sub that calls a sub ......   If not I will have to look at restricting the subs.
RWayneH

ASKER
Even when I copied the Save_XML out of its own sub and moved it one layer up, they are still Empty.  It changes worksheets and as soon as the worksheet tab changes the values disappear.  How do I hold the variable value to use in the InputBox?
Rey Obrero (Capricorn1)

<How do I hold the variable value to use in the InputBox? >
why is the need for the input box when you already have a value for "strFileName"

just comment or remove the line with Inputbox
Your help has saved me hundreds of hours of internet surfing.
fblack61
RWayneH

ASKER
I think we are close, because it is keeping the values now..  File001  002 ....  but when the InputBox shows it is blank...  What did I screw up in the InputBox statement to show?

   strFileName = InputBox("Please enter a filename or click OK to accept the default.", "Save File", _
                str) '<--- change to new default
RWayneH

ASKER
To answer your other question, I will use it with an InputBox and without.  We want the user to be able to edit the default name or leave it as is.
Rey Obrero (Capricorn1)

to declare a global variable,
 remove the declaration inside the Sub
declare it at the top of any regular moduleoption explicit

Global str as string
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
RWayneH

ASKER
All is good, except for getting it to show in the InputBox...  It is blank, but str is defined correctly.  How would I edit the following to work with the variable str?

strFileName = InputBox("Please enter a filename or click OK to accept the default.", "Save File", _
                 str) '<--- change to new default
Rey Obrero (Capricorn1)

what did you put in the sub Save_XML(??)

try using
sub Save_XML(str)

strFileName = InputBox("Please enter a filename or click OK to accept the default.", "Save File", _
                  str)
RWayneH

ASKER
Figured it out....  I was getting an error in the Call Save_XML(str).  So I took that out.  Putting in a True/False to ask user if they want to use edit file names or use suggested default...  Thanks for the help...  I am still trying to grab the global variable concept... so I can press them from sub to sub...  I like calling to existing Subs that I write for specific things, then do a lot of calling to them, (reduces rewrites and copy/pasting) but if I am going to continue doing that... I want to define globals more than not.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rey Obrero (Capricorn1)

< I was getting an error in the Call Save_XML(str).  >
what is the error?
RWayneH

ASKER
It was a Compile Error:  Wrong number of arguments or invaled assignment.  Is it because your was Sub Call Save_XML(str) and mine had Call Save_XML(str)?   This is what is calling the already defined str value right?
Rey Obrero (Capricorn1)

<Is it because your was Sub Call Save_XML(str)> where did you get this?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
RWayneH

ASKER
Thank you for the help, I really appreciate it.