Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Questions, Questions

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok then make it simpler. How do I get two buttons, Yes and No, with a question: Would you like to save?

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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/viewtop...4684&forum=2&4

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  7. #7
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •