0

The macro ,upon opening the workbook, will look to see if cell "C27" contains any of the following Text: Location1, Location2, Location3, or Location4. If they do then it will continue to save 2 copy files of the template by those locations. If not then it will open a UserForm To select the correct location from a ComboBox.

How could i reset the check after the UserForm is closed, I tried Call Auto_Open after the Unload me but it didnt work.

Macro

 Sub Auto_Open()

With Range("B30")
.Value = Time
.NumberFormat = "h-mm-ss AM/PM"
End With

Dim FileName    As String
Dim FilePath    As String
Dim FileDate    As String

Select Case Range("C27").Value

    Case "Location1", "Location2", "Location3", "Location4"

        FilePath = "C:\Users\aholiday\Desktop\FRF_Data_Macro_Insert_Test"
        FileName = Sheets("Data").Range("C27").Text

        Application.DisplayAlerts = False

        ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName

        Dim FileCopyName    As String
        Dim FileCopyPath    As String
        Dim FileTime        As String

        FilePath = "C:\Users\aholiday\Desktop\Backup"
        FileName = Sheets("Data").Range("C27").Text
        FileTime = Sheets("Data").Range("B30").Text

        ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName & Space(1) & FileTime & ".xlsx", FileFormat:=xlOpenXMLWorkbook

        MsgBox "File was saved! Ready for Next Test, Please Exit."


    Case Else

        MsgBox "File was not saved, Please Insert The Correct Testing Location"

        UserForm.Show

    Exit Sub

End Select

    Application.DisplayAlerts = True

 End Sub

UserForm

Private Sub UserForm_Initialize()

  'Empty TestLocation Box
   TestLocation.Clear

  'Fill TestLocation Box
  With TestLocation
  .AddItem "Location1"
  .AddItem "Location2"
  .AddItem "Location3"
  .AddItem "Location4"
  End With

End Sub

'---------------------

Private Sub Insert_Click()

Sheets("Data").Activate

Range("C27").Value = TestLocation.Value

End Sub

'--------------------

Private Sub CloseBox_Click()

Unload Me

End Sub
Duraholiday
  • 111
  • 1
  • 3
  • 14
  • `Range("B27")` from what worksheet? –  Aug 13 '15 at 17:53
  • I meant to change that to C27, It doesn't matter I fixed It and it still does the same thing. Its weird cause if i change it using the UserForm it works but not if i open the workbook with the correct Location there it doesn't. – Duraholiday Aug 13 '15 at 18:12
  • ohhhh I see what your saying now, hang on let me clarify that to the macro – Duraholiday Aug 13 '15 at 18:18
  • Simple Typo, Location1 was in the case statement twice, leaving out location3... Which I was testing... My second Question is still unanswered. – Duraholiday Aug 13 '15 at 18:28
  • OK, try `Select Case Sheets("Data").Range("C27").Value` since that is the worksheet you `.Activate` to store the value. –  Aug 13 '15 at 18:31
  • I fixed it, It was because i wasn't telling it to look for location3 in the code, Because i had a typo (loaction1 twice) and because i was using location3 as the testing location. – Duraholiday Aug 13 '15 at 18:40

1 Answers1

2

By using the following code for the insert button:

Private Sub Insert_Click()

   Sheets("Data").Range("C27").Value = TestLocation.Value
   Auto_Open
End Sub

The code will work (tested it), as long as you have the Auto_Open code in a module.

If you put the Auto_Open sub in the ThisWorkbook then move it to the module.

Then use the following code in ThisWorkbook:

Private Sub Workbook_Open()
    Auto_Open
End Sub

Also:

Case "Location1", "Location2", "Location1", "Location4"

Should be:

Case "Location1", "Location2", "Location3", "Location4"
DragonSamu
  • 1,163
  • 10
  • 18