Macro to do one thing 1st time file opened - do somethin else in future openings

[ILoveStMartin]

Good morning – Been trying for ovewr 5 hrs to put together pieces of code from here but failing to make all this happen - Really appreciate any help!
When Master is run for 1st time Auto_Open to ask for job name. If number entered into input box – must re-ask for job name until answer is text or file closed via cancel button on input box. When job name given, macro needs to enter that into cell named Job_Name (which has word master in it until replaced by macro with input box data) and then save the file with job name + date + time.
After file saved w name, auto_open macro needs to skip the input box and just go to a specific cell of one of the worksheets each time file opened in the future - but not to the worksheet / cell it went to the first time.
Thanks for your help -

[rvasquez]

Could you attach an example workbook?

Thanks!

[rvasquez]

Hello there,

Attached is an example workbook that I believe accomplishes what you are attempting to do. I have inserted comments in the code that appear in green to help you understand what I've done. The codes are located in the workbook on open event and then also in Module1. Please let me know if you have any questions and if this works for you!

P.S.

You might want to hit Cancel on the inputbox when you first open the document and then select No. Then save the workbook into a folder. Close the document and reopen it. This way when the code runs you will be able to find the newly saved document as the code saves it in the folder the current workbook is in.

Thanks!

[ILoveStMartin]

WOW - Oh WOW!!!
Thank you - Thank you - A hundred thousand times!

When I look at your code and then at what I have done (a guy has to start somewhere) I have to wonder - is there a possibility this part of mine will not always follow through with what I want it to do?
Your thoughts - Please
Code[]
Sheets("Job Info").Select
ThisFile = Range("B3").Value
Sheets("Weekly Billing Summary").Select
Range("C12").Select
ActiveWorkbook.SaveAs Filename:="C:\$ a For Wendi\$ Test of Macro\" & ThisFile & " " & Format(Now, "mm-dd-yyyy hh-mm-ss")
FileExtStr = "xlsm": FileFormatNum = 52
Application.ScreenUpdating = True
End Sub
Code[]

[rvasquez]

Hey there,

For the question you asked me in your message.

Add the following two lines

     ThisWorkbook.Save
                ThisWorkbook.Close

Below the code

Sub txtinputbox()
Dim str As String
    
    str = InputBox(Prompt:="Enter Job Name." & vbNewLine & "Job Name must be text not numerical", Title:="JOB NAME")    'inputbox asking for user to enter job name
        If str = vbNullString Then  'if nothing is entered then
            If MsgBox("Job Name not entered, would you like to enter a job name?", vbYesNo) = vbNo Then 'ask if user would like to enter job name, if they select no then

So that the entire code looks like this

Option Explicit
Global i As Long

Sub txtinputbox()
Dim str As String
    
    str = InputBox(Prompt:="Enter Job Name." & vbNewLine & "Job Name must be text not numerical", Title:="JOB NAME")    'inputbox asking for user to enter job name
        If str = vbNullString Then  'if nothing is entered then
            If MsgBox("Job Name not entered, would you like to enter a job name?", vbYesNo) = vbNo Then 'ask if user would like to enter job name, if they select no then
                ThisWorkbook.Save
                ThisWorkbook.Close
            Else
                txtinputbox 'run this macro again to give user inputbox again
            End If
                    
        ElseIf IsNumeric(str) Then  'if the user enters a number then
            MsgBox "You have entered an invalid Job Name." & vbNewLine & "Job Name must be text", vbOKOnly  'tell them to enter a job name text only
                txtinputbox 'rerun this macro to provide user with inputbox again
        
        Else
            With Sheets("Sheet1")   'with the worksheet sheet1
                .Range("Job_Name").Value = str  'set the named range Job_Name equal to the value entered into the input box
                .Range("A1").Value = 2    'enters the number 2 into A1 to place as a reference that the code has been run and for the on open event for this workbook
            End With    'end with
            
            'the new workbook will be saved in the same folder this workbook is saved in (defined in the Thisworkbook.path)
            With ThisWorkbook   'save this workbook as the named range Job_Name value and the current data and time
                .SaveAs Filename:= _
                ThisWorkbook.Path & "/" & Range("Job_Name").Value & "_" & VBA.Format(VBA.Now, "mm_d_yyyy (h_mm)") & ".xlsm" _
                , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
                
            End With
        
        End If
        
        
End Sub