Macro prompts user to run macro?
 

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


--
miwarren
------------------------------------------------------------------------
miwarren's Profile: http://www.excelforum.com/member.php...o&userid=24682
View this thread: http://www.excelforum.com/showthread...hreadid=477638

Macro prompts user to run macro?
 

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


--
Brett0769
------------------------------------------------------------------------
Brett0769's Profile: http://www.excelforum.com/member.php...o&userid=28217
View this thread: http://www.excelforum.com/showthread...hreadid=477638

Macro prompts user to run macro?
 

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

Macro prompts user to run macro?
 

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

Macro prompts user to run macro?
 

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

HELP?


Jim Thomlinson Wrote:
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
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



--
miwarre
-----------------------------------------------------------------------
miwarren's Profile: http://www.excelforum.com/member.php...fo&userid=2468
View this thread: http://www.excelforum.com/showthread.php?threadid=47763

Macro prompts user to run macro?
 

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


Code
-------------------
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 Su
-------------------


Jim Thomlinson Wrote:
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
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



--
miwarre
-----------------------------------------------------------------------
miwarren's Profile: http://www.excelforum.com/member.php...fo&userid=2468
View this thread: http://www.excelforum.com/showthread...hreadid=477638