Msg Box to appear once on opening

[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

 

[royUK]

That is the purpose of the macro isn't it? What determines when to run it? The only thing that you can do is record the fact that it has been run in a cell somewhere 7 check that cell to decide whether to run it.

 

[sujittalukde]

The macro has to run only one time on opening the workbook. Suppose it is attached to Book1

The book1 is opened. The user working, and then run the code.

Mesbox will display.

If next time he run the macro, no such msgbox to display.

The user closed the WB.

Opened again on same day or next day or whwnever, and run the code, the msgbox will display. from next time, no msgbox.

 

[royUK]

Store a flag in a remote cell & check when opening. Note your code does not actually convert Formulas to Values, it rounds them

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

 

[sujittalukde]

Thanks royUK for the reply.
But this flag concept is new to me. Can you throw some light on flag concept? Also where to put the code, in a standard module or This workbook module?

 

[sujittalukde]

Some help please!!!!!!!111

 

[sujittalukde]

ASlo posted here to get help.Hope there it will solved
http://vbaexpress.com/forum/showthread.php?p=108390#post108390

 

[royUK]

try reading the code & following the process, and testing it in a copy workbook before cross posting.

My suggestion checks a cell for an entry, if it empty it runs your code & places a 1 in the cell. next time that workbook is opened it will not run the code because the cell has an entry.

I have also pointed out that your code does not replace formulas with values.

 

[jim may]

RoyUK,
When I run stepping through (on this line) I get R/T 13 - Type Mismatch
and I guess it's because of the "" empty string - Why the + "" ?

If rFlag.Value + "" Then

Thanks in advance..

 

[sujittalukde]

THe code was provided ast vbaexpress at above link and as per my requirement.
Thanks to royuk and rory for their help and support.