Change code to require a Response

**Aeneren** · 04-21-2012, 11:52 PM

Hello All,

Ok ive been asked by the boss to make some changes to the code below:

Boss want the user's to be required to fill in the Two fields before being aloud to procede to the next step in the inventory.
I marked the fields that require a response:

Sub NameIt()

Dim fileSaveName  As String
Dim strOrigDate   As String
Dim strFileDate   As String
Dim strFileNum    As String
Dim strFileLoc    As String
Dim strFileName   As String

strFileLoc = "C:\Users\Brian\Desktop\Excell Test folder\"
strOrigDate = CStr(Sheets("Log In").Range("D3"))
strFileDate = Format(strOrigDate, ("m-d-yyyy"))
strFileNum = CStr(Sheets("Log In").Range("F5"))    'Require a User Response
strFileName = CStr(Sheets("Log In").Range("B5"))  'Require a User Response

   fileSaveName = strFileLoc & strFileDate & "-" & strFileNum & "-" & strFileName & ".xlsm"
   ActiveWorkbook.SaveAs Filename:=fileSaveName
  
End Sub

As always thank's for Your Help with this

Aeneren

**AlvaroSiza** · 04-22-2012, 12:06 AM

Perhaps change your strFileNum and strFileName to:

strFileNum = InputBox(Prompt:="Please enter a file number.", _
                     Title:="Enter File No", Default:="File No. Here")

strFileName = InputBox(Prompt:="Please enter a file name.", _
                     Title:="Enter File Name", Default:="File Name Here")

Please report back.

-as-

**Aeneren** · 04-22-2012, 12:29 AM

AlvaroSiza,

Thanks for the Response, Your change does work but i am looking more along the lines of the user having to pick there name and Truck number from the Data Validation Lists that are already in the workbook.

Ive attached a small example file this time for your viewing.

Aeneren

Inventory Sample-3.xlsm

**Paul** · 04-22-2012, 12:29 AM

You could add an IF statement before the Save line, e.g.

If Len(strFileNum) = 0 or Len(strFileName) = 0 Then
    MsgBox "You haven't filled in cells B5 and/or F5 on the Log In sheet."
    Exit Sub
End If

**Aeneren** · 04-22-2012, 12:40 AM

Paul,

Thank you for your response as well

All 3 of them lol, thinking the boards got a glitch tonight

Aeneren

**Paul** · 04-22-2012, 12:46 AM

Glitch seems to be an understatement. Just deleted all the duplicate responses..

**Aeneren** · 04-22-2012, 01:19 AM

Paul,

Your Code is working a Msg box comes up If one or both fields are open, but as soon as i click OK it advances to the next Sheet.

Sub NameIt()

Dim fileSaveName  As String
Dim strOrigDate   As String
Dim strFileDate   As String
Dim strFileNum    As String
Dim strFileLoc    As String
Dim strFileName   As String

strFileLoc = "C:\Users\Brian\Desktop\Excell Test folder\"
strOrigDate = CStr(Sheets("Log In Screen").Range("F2"))
strFileDate = Format(strOrigDate, ("m-d-yyyy"))
strFileNum = CStr(Sheets("Log In Screen").Range("D6"))
strFileName = CStr(Sheets("Log In Screen").Range("I6"))

   If Len(strFileNum) = 0 Or Len(strFileName) = 0 Then
    MsgBox "You haven't filled in MEDIC# and/or CREW Yet."
        End If
   fileSaveName = strFileLoc & strFileDate & "-" & strFileNum & "-" & strFileName & ".xlsm"
   ActiveWorkbook.SaveAs Filename:=fileSaveName
  
End Sub

is it possible the macro that the Start Inventory is activating is the cause.

Private Sub CommandButton1_Click()

    Call NameIt
    Inventory_Home
    
    
End Sub

Thanks Aeneren

**Paul** · 04-22-2012, 05:59 AM

You forgot to keep in the "Exit Sub" before the "End If". Also, it's great that you created all of those variables. If you want to continue using them you can, but it could also be setup like the following (also added a check to ensure there was a valid date in F2):

Sub NameIt()
With Sheets("Log In Screen")
    If .Range("F2") = "" Or .Range("D6") = "" Or .Range("I6") = "" Then
        MsgBox "You haven't filled in the Date, MEDIC# and/or Crew yet."
        Exit Sub
    ElseIf Not IsDate(.Range("F2").Value) Then
        MsgBox "The Date (F2) is not valid."
        Exit Sub
    Else
        ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Brian\Desktop\Excell Test Folder\" & _
        Format(.Range("F2").Value, "m-d-yyyy") & "-" & _
        CStr(.Range("I6").Value) & "-" & CStr(.Range("D6").Value) & ".xlsm", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled
    End If
End With
End Sub