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:
[VBA]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
[/VBA]

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.