Questions, Questions

Frankie needs HELP

New Member
Joined
Apr 3, 2002
Messages
43
I need to produce a message box containing two buttons (Yes and No.

When you click on the Yes I need to make my spreatsheet ("Analysis") move to a new workbook and the user prompted to enter a filename where the workbook should be saved, including allowing saving to a different folder or drive. It should also automatically close the macro down.

If No is clicked, the macro should simply terminate, and leave the worksheets visable.

HELP!!!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Ok, to answer the first part, the message box can be done like this...

Sub test()

Dim MyCheck

MyCheck = MsgBox("Would you like to save?", vbYesNo)
If MyCheck = vbYes Then
' put what to do if they say yes here
ElseIf MyCheck = vbNo Then
' put what to do if they say no heer
End If


End Sub

As to the rest, you can probably record most of the copying your sheet to a new workbook bit, and then you might find help on the SaveAs part here...
http://www.mrexcel.com/board/viewtopic.php?topic=4684&forum=2&4
 
Upvote 0
Try the following:

Sub SaveWorkbook()
messagebox = MsgBox("Export Worksheet", vbYesNo)
If messagebox = vbYes Then
Dim NEWBOOK, FNAME
Application.SheetsInNewWorkbook = 1
Set NEWBOOK = Workbooks.Add
FNAME = Application.GetSaveAsFilename _
(InitialFilename:="NewWorkbook")
If FNAME <> "False" Then
NEWBOOK.SaveAs Filename:=FNAME
End If
ThisWorkbook.Activate
Sheets("Analysis").Copy Before:=NEWBOOK.Sheets(1)
Application.DisplayAlerts = False
NEWBOOK.Worksheets("Sheet1").Delete
End If
GoTo 1

1 End Sub
 
Upvote 0
Al, the line (InitialFilename:="NewWorkbook") is coming up with an error, Expected:line number or label or statement or end of statement comes up. What am I doing wrong?
 
Upvote 0
I'm still struggling. I've got the yes no message popping up and when I click yes it puts the Analysis worksheet into a new work book but I also need a prompt to enter a filename where the workbook should be saved, and also allowing saving to a different folder or drive. HELP!
 
Upvote 0
Try the following, I got rid of that line (it works fine for me, XL2000):

Sub SaveWorkbook()
messagebox = MsgBox("Export Worksheet", vbYesNo)
If messagebox = vbYes Then
Dim NEWBOOK, FNAME
Application.SheetsInNewWorkbook = 1
Set NEWBOOK = Workbooks.Add
FNAME = Application.GetSaveAsFilename
If FNAME <> "False" Then
NEWBOOK.SaveAs Filename:=FNAME
End If
ThisWorkbook.Activate
Sheets("Sheet1").Copy Before:=NEWBOOK.Sheets(1)
Application.DisplayAlerts = False
NEWBOOK.Worksheets("Sheet1").Delete
End If
Application.DisplayAlerts = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top