hypuk
10-14-2009, 10:18 AM
Hi guys
I'm new to VBA and was wondering how I can add a messagebox to this button, once it's pressed also i needa reference to 2 of my cells included in there as well it's a reference number and a surname)
Below is the code for my command button already
Any help would be much appreciated
Thanks
Mark
Sub SaveFileAsDate()
Dim WSName As String, CName As String, Directory As String, savename As String
''''''''''''''''''''CHANGE THE NEXT 3 LINES TO FIT YOUR NEEDS'''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
WSName = "Form"
'change "Sheet1" to sheet tab name containing cell reference
CName = "D9"
DName = "B4"
'change "A1" to the cell with your date
Directory = "U:\pipeline_offline\"
'directory you want to save to--(make sure string ends with forward slash \)
'...to save to default directory change to "" (Null)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
savename = Sheets(WSName).Range(CName).Text
savename = savename & Sheets(WSName).Range(DName).Text
If Directory = "" Then Directory = CurDir & "\"
On Error GoTo errorsub:
ActiveWorkbook.SaveAs Filename:=Directory & savename & ".xls"
Exit Sub
errorsub:
Beep
MsgBox "Changes not saved!", vbExclamation, Title:=savename & ".xls"
End Sub
I'm new to VBA and was wondering how I can add a messagebox to this button, once it's pressed also i needa reference to 2 of my cells included in there as well it's a reference number and a surname)
Below is the code for my command button already
Any help would be much appreciated
Thanks
Mark
Sub SaveFileAsDate()
Dim WSName As String, CName As String, Directory As String, savename As String
''''''''''''''''''''CHANGE THE NEXT 3 LINES TO FIT YOUR NEEDS'''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
WSName = "Form"
'change "Sheet1" to sheet tab name containing cell reference
CName = "D9"
DName = "B4"
'change "A1" to the cell with your date
Directory = "U:\pipeline_offline\"
'directory you want to save to--(make sure string ends with forward slash \)
'...to save to default directory change to "" (Null)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
savename = Sheets(WSName).Range(CName).Text
savename = savename & Sheets(WSName).Range(DName).Text
If Directory = "" Then Directory = CurDir & "\"
On Error GoTo errorsub:
ActiveWorkbook.SaveAs Filename:=Directory & savename & ".xls"
Exit Sub
errorsub:
Beep
MsgBox "Changes not saved!", vbExclamation, Title:=savename & ".xls"
End Sub