Macro prompts user to run macro?

[miwarren]

Is there any way I can set a "open macro" to ask the user if they would like to "update flags" run a seperate macro?

I have it set where the workbook runs a macro on open to update the flags, but if they have already opened it recently then they probably don't need to update the flags again, so I would like to give them a prompt where they can so yes or no to the update and not have to wait on the macro to run everytime.

Any suggestions...?

Thanks

[Brett0769]

Use a message box to get the choice from the user, here's a sample.

Dim intYN As Integer
intYN = MsgBox("Do you want to update flags?", vbYesNo, "Update Flags")
If intYN = 6 then
[DoIt]
Else
[Don't]
End If

[miwarren]

It did prompt me, but it ran the macro no matter which you chose.
Anymore help???

Thanks

Use a message box to get the choice from the user, here's a sample.

Dim intYN As Integer
intYN = MsgBox("Do you want to update flags?", vbYesNo, "Update Flags")
If intYN = 6 then
[DoIt]
Else
[Don't]
End If

[Jim Thomlinson]

Give this a try...

If MsgBox("Do you want to update flags?", vbYesNo, "Update
Flags") = vbYes then
[DoIt]
Else
[Don't]
End If

--
HTH...

Jim Thomlinson


"miwarren" wrote:

>
> It did prompt me, but it ran the macro no matter which you chose.
> Anymore help???
>
> Thanks
>
> Brett0769 Wrote:
> > Use a message box to get the choice from the user, here's a sample.
> >
> > Dim intYN As Integer
> > intYN = MsgBox("Do you want to update flags?", vbYesNo, "Update
> > Flags")
> > If intYN = 6 then
> > [DoIt]
> > Else
> > [Don't]
> > End If
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=477638
>
>

[miwarren]

When I input this into the module it immediately gives me a compile error message.
"Expected: line number or label or statement or end of statment

HELP?

Give this a try...

If MsgBox("Do you want to update flags?", vbYesNo, "Update
Flags") = vbYes then
[DoIt]
Else
[Don't]
End If

--
HTH...

Jim Thomlinson


"miwarren" wrote:

>
> It did prompt me, but it ran the macro no matter which you chose.
> Anymore help???
>
> Thanks
>
> Brett0769 Wrote:
> > Use a message box to get the choice from the user, here's a sample.
> >
> > Dim intYN As Integer
> > intYN = MsgBox("Do you want to update flags?", vbYesNo, "Update
> > Flags")
> > If intYN = 6 then
> > [DoIt]
> > Else
> > [Don't]
> > End If
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=477638
>
>

[miwarren]

I made some changes and now it doesn't run the macro no matter which you pick. I will just post the code and maybe someone can tell me where I am going wrong. Thanks

Sub Auto_Open()

If MsgBox("Do you want to update flags?", vbYesNo, "UpdateFlags") = vbYes Then
[DoIt]
Else
[Don't]
End If
End Sub
Sub UpdateFlags()
Sheets("Oct").Select
Range("A2").Select
With ActiveSheet.Shapes.AddShape(msoShapeCloudCallout, 200, 150, 150, 100)
.Name = "EFlag"
.TextFrame.Characters.Text = "Updating Flags    Please Wait..."
.TextFrame.HorizontalAlignment = xlHAlignCenter
.TextFrame.VerticalAlignment = xlVAlignCenter
End With
Application.OnTime Now, "Flags"
End Sub
Sub Flags()
'
' Exception Form Flags
' Macro recorded 10/17/2005 by mwarren
'

'
    Application.ScreenUpdating = False
    Workbooks.Open(Filename:="I:\SECURED\B&H Reconciliations\Admin\log.xls"). _
        RunAutoMacros Which:=xlAutoOpen
    Windows("Borders 2005.xls").Activate
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "E-FORM"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "READY"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "CONCLUSION"
    Range("J1:L1").Select
    Selection.Font.Bold = True
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-6],[log.xls]Sheet1!C1:C2,1,FALSE)>0,""YES"",""NO"")"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],[log.xls]Sheet1!C1:C2,2,FALSE)"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],[log.xls]Sheet1!C1:C3,3,FALSE)"
    Range("J2:L2").Select
    Selection.AutoFill Destination:=Range("J2:L2000"), Type:=xlFillDefault
    Range("J2:L2000").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("J1:L1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("J:L").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("J:L").EntireColumn.AutoFit
    Range("M2").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Windows("log.xls").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
    Range("A1").Select
    
    Application.OnTime Now, "FlagsOver"
End Sub
Sub FlagsOver()
ActiveSheet.Shapes("EFlag").Delete
ActiveWorkbook.Save
End Sub

Give this a try...

If MsgBox("Do you want to update flags?", vbYesNo, "Update
Flags") = vbYes then
[DoIt]
Else
[Don't]
End If

--
HTH...

Jim Thomlinson


"miwarren" wrote:

>
> It did prompt me, but it ran the macro no matter which you chose.
> Anymore help???
>
> Thanks
>
> Brett0769 Wrote:
> > Use a message box to get the choice from the user, here's a sample.
> >
> > Dim intYN As Integer
> > intYN = MsgBox("Do you want to update flags?", vbYesNo, "Update
> > Flags")
> > If intYN = 6 then
> > [DoIt]
> > Else
> > [Don't]
> > End If
>
>
> --
> miwarren
> ------------------------------------------------------------------------
> miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
> View this thread: http://www.excelforum.com/showthread...hreadid=477638
>
>