PDA

View Full Version : Message box to appear once commandbutton is pressed?



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

p45cal
10-14-2009, 10:39 AM
MsgBox savename
directly after:
savename = savename & Sheets(WSName).Range(DName).Text

?

or

MsgBox savename & vblf & range("D3").value & ", " & range("T12").value

where D3 and T12 are the two cells you want to refer to.

hypuk
10-14-2009, 10:57 AM
Hi P45CAL

Thanks for the reply, the message box appears but it appears to early. I only want it to appear when I click on the command button :)

Thanks
Mark

p45cal
10-14-2009, 11:04 AM
In your original post you said "Below is the code for my command button already". I don't see where else this code should go. Are we talking about the same command button?

hypuk
10-14-2009, 11:16 AM
Hi P45CAL

Sorry my fault, it does work, although I'm getting the cell values appearing twice

Thanks
Mark