Solved: Msg Box to appear once on opening a WB

[sujittalukde]

I am using the following to code with a warning message. However this warning message appears each time the macro is run? Can this be modified in such a manner so that it will show the warning message only once for first time it is run on each opening of WB?



Sub mln()
Dim a
a = MsgBox("Please note that this will replace formulae with value.So you are requested to run this on a copy of the file.", vbYesNo + vbExclamation, "Important")
If a = vbYes Then
For Each c In Selection

c.Value = Application.WorksheetFunction.Round(c / 1000000, 2)
Next c
End If

End Sub


Also posted here
http://www.mrexcel.com/board2/viewtopic.php?t=284973

A solution to this was provided at mrexcel,but not able to run the code. I dont know whether the code is running well or not as I am not able to run the code. Can someone here please help me to run the code?

Actually now the problem to me is the "flag" concept. What is this & how can I use this to run the code?

The code provided there is



Option Explicit

Private Sub Workbook_Open()
Dim rFlag As Range
Dim a

Set rFlag = Sheet1.Cells(.Rows.Count, .Columns.Count)

If rFlag.Value + "" Then
a = MsgBox("Please note that this will replace formulae with value.So you are requested to run this on a copy of the file.", vbYesNo + vbExclamation, "Important")
If a = vbYes Then
'this code will not convert to value!
'For Each c In Selection
' c.Value = Application.WorksheetFunction.Round(c / 1000000, 2)
'Next c
'this will
Worksheets.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Select
Application.CutCopyMode = False
'set flag
rFlag.Value = 1
Else: End
End If
End If
End Sub

[rory]

Do you mean something like this:
Sub mln()
Static blnAlreadyRun As Boolean
Dim a
Dim c As Range
Dim strMsg As String
If Not blnAlreadyRun Then
strMsg = "Please note that this will replace formulae with value." & vbCrLf & _
"So you are requested to run this on a copy of the file."
a = MsgBox(strMsg, vbYesNo + vbExclamation, "Important")
blnAlreadyRun = True
Else
a = vbYes
End If
If a = vbYes Then
For Each c In Selection
c.Value = Application.WorksheetFunction.Round(c.Value / 1000000, 2)
Next c
End If
End Sub


Regards,
Rory

[royUK]

I think he wants to only run the code once on opening the workbook, so that is why I recommended storing a value in a cell.

[rory]

My impression was he only wants the message to appear the first time the code is run for any given time the workbook is opened. I guess we'll have to wait and see...
Rory

[sujittalukde]

Thanks rory, actually your code is what I was looking for.

Thanks to royuk also for his help and support.