Hi,
I'm a VBA beginner and was after some advice on how to use the ChDrive/ChDir methods.
I've written the macro shown below to check if the title (cell A1) contains "Template" and if it does, then display the message. If ok is pressed (or if cell A1 dosn't contain "Template") then change the save as directory to the one shown below.
This all works fine upto the change of drive/directory (it dosn't change).
I've had a look at the GetSaveAsFilename method. But I don't need to save it. I just need to set the save as directory.
What am I doing wrong with the ChDrive/ChDir method? Can anyone offer any advice?
Thanks,
Roy
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As String
If SaveAsUI Then
i = Right(Range("A1"), 8)
If i = "Template" Then
If MsgBox("Region and project title not set. Are you sure you want to save?", vbOKCancel) = vbOK Then
ChDrive ("O")
ChDir ("O:\# BAU Testing Programme\BAU POM")
Exit Sub
Else
Cancel = True
End If
Else
ChDrive ("O")
ChDir ("O:\# BAU Testing Programme\BAU POM")
End If
End If
End Sub
I'm a VBA beginner and was after some advice on how to use the ChDrive/ChDir methods.
I've written the macro shown below to check if the title (cell A1) contains "Template" and if it does, then display the message. If ok is pressed (or if cell A1 dosn't contain "Template") then change the save as directory to the one shown below.
This all works fine upto the change of drive/directory (it dosn't change).
I've had a look at the GetSaveAsFilename method. But I don't need to save it. I just need to set the save as directory.
What am I doing wrong with the ChDrive/ChDir method? Can anyone offer any advice?
Thanks,
Roy
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim i As String
If SaveAsUI Then
i = Right(Range("A1"), 8)
If i = "Template" Then
If MsgBox("Region and project title not set. Are you sure you want to save?", vbOKCancel) = vbOK Then
ChDrive ("O")
ChDir ("O:\# BAU Testing Programme\BAU POM")
Exit Sub
Else
Cancel = True
End If
Else
ChDrive ("O")
ChDir ("O:\# BAU Testing Programme\BAU POM")
End If
End If
End Sub