Msg Box to appear once on opening

sujittalukde

Well-known Member
Joined
Jun 2, 2007
Messages
520
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?
Code:
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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

Well-known Member
Joined
Jun 2, 2007
Messages
520
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

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Store a flag in a remote cell & check when opening. Note your code does not actually convert Formulas to Values, it rounds them

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

Well-known Member
Joined
Jun 2, 2007
Messages
520
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?
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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

Well-known Member
Joined
Jun 2, 2007
Messages
520
THe code was provided ast vbaexpress at above link and as per my requirement.
Thanks to royuk and rory for their help and support.
 

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top