Looping until I hit Yes in message box

Folks,
In my Worksheet_Activate event I have the following code:
Private Sub Worksheet_Activate()
Dim Response
Response = MsgBox("Continue working on a project?", vbYesNo)
If Response = vbNo Then
ReplaceFonts
End If
End Sub

Open in new window

My objective is to allow a user to continue work on a worksheet or delete the data by calling
Sub ReplaceFonts()
'
' ReplaceFonts Macro
'

'
    Range("A3:I42").Select
    Selection.ClearContents
    Range("K11").Select
    ActiveWindow.ScrollWorkbookTabs Sheets:=10
    Sheets("Data").Select
    Range("A3:I42").Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-2
    Sheets("FontsFillsAlignments").Select
    Range("A3").Select
    ActiveSheet.Paste
End Sub

Open in new window

Frank FreeseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Do Until MsgBox("Continue working on a project?", vbYesNo) = vbYes
    If Response = vbNo Then
    ReplaceFonts
End If
Loop
0
Frank FreeseAuthor Commented:
Not exactly,
If the user select Yes then leave the workbook as is. If the select No the call ReplaceFonts, which simply overwrites anything done with original data. I continue to loop until I select Yes.
0
Martin LissOlder than dirtCommented:
I'm sorry.

Do Until MsgBox("Continue working on a project?", vbYesNo) = vbNo
    ReplaceFonts
Loop

If I've got it backward then change vbNo to vbYes.
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Yes, you will have to either change to vbYes, or replace Until by While, to get it right.
0
KromptonCommented:
Is your user working on a single worksheet/workbook?

It might be better to use Worksheet_Deactivate or Workbook_BeforeClose and ask your question then?

Cheers,
Krompton
0
Frank FreeseAuthor Commented:
Attached is the workbook - still looping. on Yes and/No. The tab is "FontsFillsAttachments"
0
KromptonCommented:
Have you considered creating a menu where you can activate the macro instead of a loop?

Krompton
0
Frank FreeseAuthor Commented:
"creating a menu where you can activate the macro instead of a loop?"
I'm not sure what you referring to? How does one do that?
0
KromptonCommented:
Here is a quick menu procedure. It will display the "Add-Ins" ribbon menu (in 2013) and add macro items. You will probably need to adjust it to suit your needs. Select Insert new module in your workbook and paste the below code.

Good luck,
Krompton


Public Function TestMenu()
On Error GoTo TrapErr

Dim cbrMenuBar As CommandBar
Dim ctlMenu As CommandBarPopup
Dim ctlMenuItem As CommandBarButton
Dim MyWorkbook As String
 
    ''' Create MenuBar
    On Error Resume Next
    Application.CommandBars("Test").Delete
    On Error GoTo TrapErr

    Set cbrMenuBar = CommandBars.Add("Test", msoBarTop, False, True)
    cbrMenuBar.Visible = True
   
            Set ctlMenu = cbrMenuBar.Controls.Add(msoControlPopup)
            ctlMenu.Caption = "MyMacros"
            ctlMenu.BeginGroup = True
            ''' Add MenuItems
            Set ctlMenuItem = ctlMenu.Controls.Add(msoControlButton)
            ctlMenuItem.Caption = "ReplaceFonts"
            ctlMenuItem.OnAction = "'" & ActiveWorkbook.Name & "'!ReplaceFonts"

ExitFunction:
    Exit Function
   
TrapErr:
    MsgBox Err.Description
    Resume ExitFunction

End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
thanks - let me chew on this some
0
Frank FreeseAuthor Commented:
Where do I place this module to use it?
0
Martin LissOlder than dirtCommented:
still looping. on Yes and/No
I don't see where you attached your workbook but I tested this and it stops as soon as 'No' is clicked.

Do Until MsgBox("Continue working on a project?", vbYesNo) = vbNo
    ReplaceFonts
Loop

Open in new window

0
KromptonCommented:
With the workbook open press "Alt+F11" to open the visual basic module.Excel VBA modulesRight-click the appropriate workbook name in the left window and select Insert>ModuleVBA ModuleDouble-Click the added module name ti the left window to verify active and paste the code in the right-hand window.
Press "F8" to walk through the code.

If you record a macro in excel, this is where you can review the code generated.

Krompton
0
Frank FreeseAuthor Commented:
Martin,
Guess it would help to attach the workbook. I've made some change as suggested. The workbook is now attached. The tab I am working with and the code for Worksheet-Activate is FontsFillsAlignment.
Learning-Excel-2013.xlsm
0
Frank FreeseAuthor Commented:
Krompton,
My question to you is in the worksheet where do I call this module?
Thanks
0
Martin LissOlder than dirtCommented:
In Excel 2010 at least, your Worksheet_Activate code for FontsFillsAlignments sheet works perfectly.
0
KromptonCommented:
Sorry,

Here is an empty excel file with the code entered. Hope this helps.

Krompton
TestMenu.xlsm
0
Frank FreeseAuthor Commented:
Martin,
Let me try this on a 2010 version. But do you understand the problem I'm having as this is an Excel 2013 workbook? Krompton,
Thanks - I'll look at it later.
0
Frank FreeseAuthor Commented:
Folks,
Thanks for everyone hanging in there with me. Let me attempt to explain what it is I am trying to accomplish.
In the worksheet tab labeled "FontsFillsAlignments" there is a data set where the user has to use fonts, fills and alignment tools. I'm working on printing the instructions for them.
Let's say a user started to work on learning more about Fonts, Fills and Alignments but does not completed the assignment. They could just save the workbook and pick-up where they left. Or they may elect to start all over. Or they completed it and someone else wants to learn more on Font, Fills, and Alignments. When the  worksheet opens the user has two options First continue where they left off or undo everything that they have done and start over. If they elect to continue where they were then the worksheet does not change. However, if the want to start over then the data set is restored (that's what the ReplaceFonts macro does. At ID: 40690791 is the file. What I'm asking for may not possible.
0
Martin LissOlder than dirtCommented:
I think your Worksheet_Activate code should be this:

Private Sub Worksheet_Activate()

If MsgBox("Continue working on a project?", vbYesNo) = vbYes Then
    Exit Sub
Else
    ReplaceFonts
End If
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Another you might do is to use a cell like "ZC1" that's off-screen and put some indicator like the word "changed" in that cell if any change is made to the worksheet. Then change the above code to the following so the user isn't asked the question unless a change has been made. You should make sure to clear that cell at the end of ReplaceFonts. I'd also make the font in that cell white and lock it.

Private Sub Worksheet_Activate()

if Range("ZC1") = "changed" then
    If MsgBox("Continue working on a project?", vbYesNo) = vbYes Then
        Exit Sub
    Else
        ReplaceFonts
    End If
End If
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Also your workbook at over 1100 KB is very large. I don't know if they apply to Excel 2013 which I assume you are using, but you should apply some of the file-size-reduction techniques we talked about in one of our previous threads. Otherwise no one will want to take the time to open the workbook after the first time.
0
Frank FreeseAuthor Commented:
I've got a lot of pictures that I'm using and that has got to be making the WB larger. Will delete them. After replacing the fan in my notebook I'll get back. Thanks
0
KromptonCommented:
There are many different ways to accomplish what you are looking to do.

You can follow the path you have already begun. There is nothing really wrong with that. Your decision depends mainly on how many individuals you are expecting to use it and if you are going to be readily available for technical support or if the application needs to be more robust because support will be limited.

One alternative would be, if you have a worksheet set to default values you want a user to start with, rather than resetting a sheet they have changed you could hide the template worksheet then when they choose to start over make a new copy of the template sheet and remove any old sheets you wish discarded. That requires far fewer lines of code to accomplish and they would always be able to save the current sheet.

Good luck,
Krompton
0
Frank FreeseAuthor Commented:
Folks,
I found my problem - I used the Worksheet_Activate event to place my code including the macros that sent me to another sheet. Therefore, when I returned to my first worksheet, the worksheet_activate event was again triggered to up pops my message box.
How stupid of me not to see that. Rather than use the Worksheet_Avitvate even I've simply created a text box and assigned a macro to it. I get what I wanted.
I'll go back and award point later today.
Thanks to all.
0
Frank FreeseAuthor Commented:
thank you folks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.