Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Messabe Box to appear once

  Asked By: Bastet    Date: Jan 29    Category: MS Office    Views: 810
  

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 is given below:
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

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Fred Hicks     Answered On: Jan 29

Not without setting some sort of "flag" or indicator to say the code  has
been run  already.

You can do this in a number of ways.... But... You can also set up the code
in the auto_open procedure. This is run when the workbook is opened.

Option explicit

Sub Auto_Open
mln
End sub


Sub mln()

Dim llAnswer as long
Dim lsMsg as string
Dim lsTitle as string
Dim rlCell as Range

lsMsg = "Please note that this will replace  formulae with value."
lsMSg = lsMsg & vbCrLf
lsMSg = lsMsg & "So you are requested  to run this on a copy  of the file."

lsTitle = "Important"

llAnswer = MsgBox(lsMsg, vbYesNo + vbExclamation, lsTitle)

If llAnswer = vbYes Then

For Each rlCell In Selection

rlCell.Value = Application.WorksheetFunction.Round(c / 1000000, 2)

Next rlCell

End If

End Sub

Note too that I've "tidied" the code up a bit.

1) Put Option Explicit at the top.
This makes sure that code running undeclared variables will error.
2) Made more meaningful names of the variables.
3) Added Dim for the c / rlCell variable.
4) Added variables for the text strings.
This makes the msgbox code more "generic".
5) Structured the code to make it more readable.

 
Answer #2    Answered By: Ludo Ricci     Answered On: Jan 29

Thanks for the reply but your is changing the number to 0 (Zero) and also
showing the msgbox each time  the code  is run

 
Answer #3    Answered By: Luigi Fischer     Answered On: Jan 29

You can put a variable at the top of your module (i.e. before the
subroutine) and set it to indicate that the question has been asked, and
answered. Then only ask the question if that variable isn't set. That
variable should then stay set for the remainder of the session.

However, I'd also suggest a different structure for your subroutine, to make
it easier to do the rest.

Instead of asking if the answer is yes (variable "a"), ask if it is no. If
it is no, do an immediate Exit Sub.

That way you can take the If away from your For statement - simply do it
every time, provided the subroutine wasn't exited early by the Exit Sub.
Then you can surround the MsgBox call and the If inside another If that
causes it only to be asked once.

 
Answer #4    Answered By: Latasha Wilson     Answered On: Jan 29

Thanks for the reply, but not able to run  the macro.

 
Answer #5    Answered By: Ora Hanson     Answered On: Jan 29

Try thinking outside the box.

My first thought is to store a value in a most likely unused cell in the
worksheet. The vast majority of workbooks do not use every single cell.
So you could easily choose the last available row, or even the last row
and column.

The idea is simple. When someone runs the code  for the first time  and
they answer "yes", store something, a number, or even the word "yes" in
this cell. You can have your code check this cell every time someone
runs the code. If "yes" appears skip the question.

For example...

Sub mln()
Dim a
If Range("IV65536").Value = "yes" Then
Call RunFunction
Else
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
Range("IV65536").Value = "yes"
Call RunFunction
End If
End If
End Sub

Sub RunFunction()
For Each c In Selection
c.Value = Application.WorksheetFunction.Round(c / 1000000, 2)
Next c
End If

End Sub

 
Answer #6    Answered By: Angel Watkins     Answered On: Jan 29

Sorry, I forgot to alter the numerator of the division.
The calculation should read...
rlCell.Value = Application.WorksheetFunction.Round(rlCell.Value /
1000000, 2)

As for running the macro  only once then it's possible I misunderstood what
you wanted.

I thought you only wanted the procedure to run  once when the workbook was
opened. The given code  will do just that. If you run the procedure on it's
own again then of course you'll get the message  box.

If you never whant a message box  again after the procedure has run once then
as I said before, you need to set some sort of "flag" to tell the procedure
that it's already been run. Try defining a public variable and setting it
when the procedure runs.

Option Explicit
Public bgDoNotRunAgain as boolean

Sub Auto_Open
mln
bgDoNotRunAgain = True
End sub

Sub mln()

Dim llAnswer as long
Dim lsMsg as string
Dim lsTitle as string
Dim rlCell as Range

If bgDoNotRunAgain then
Exit sub
endif

lsMsg = "Please note that this will replace  formulae with value."
lsMSg = lsMsg & vbCrLf
lsMSg = lsMsg & "So you are requested  to run this on a copy
of the file."

lsTitle = "Important"

llAnswer = MsgBox(lsMsg, vbYesNo + vbExclamation, lsTitle)

If llAnswer = vbYes Then

For Each rlCell In Selection

rlCell.Value = Application.WorksheetFunction.Round(c / 1000000, 2)

Next rlCell

End If

End Sub

 
Answer #7    Answered By: Burkett Bernard     Answered On: Jan 29

You need to
describe the exact symptoms and include the relevant code  inline in a
message.

 
Didn't find what you were looking for? Find more on Messabe Box to appear once Or get search suggestion and latest updates.




Tagged: