Run Once Only
 

Hi, how do I make a macro run one time only? It will not run agai
unless I close and open the spreadsheet again

--
gtto
-----------------------------------------------------------------------
gtton's Profile: http://www.excelforum.com/member.php...fo&userid=2472
View this thread: http://www.excelforum.com/showthread.php?threadid=38330

Run Once Only
 

Good evening gtton

The most obvious way would be to run the macro automatically on opening
the file. To do this either name the macro auto_open() or set it up as
an event procedure in the ThisWorkbook pane calling it Workbook_Open().
Either of these will call the macro to run once the file is opened and
will not run again, unless called.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=383303

Run Once Only
 

Hi Dominic, my macro deletes 4-5 cell rows when you hit a button.
prefer if the user does not hit the button more than once because the
may delete important information further down the spreadsheet

--
gtto
-----------------------------------------------------------------------
gtton's Profile: http://www.excelforum.com/member.php...fo&userid=2472
View this thread: http://www.excelforum.com/showthread.php?threadid=38330

Run Once Only
 

You could include a setting in your code that writes to say a hidden sheet
or range name. E.G using the latter

Sub StopCodeRunning()
If Application.Names("Switch").Value = "=TRUE" Then
Exit Sub
End If
Application.Names("Switch").Value = "=TRUE"
End Sub

You will need to reset the name to =FALSE in the workbook_close() event or
similar

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"gtton" wrote in
message ...

Hi, how do I make a macro run one time only? It will not run again
unless I close and open the spreadsheet again.


--
gtton
------------------------------------------------------------------------
gtton's Profile:
http://www.excelforum.com/member.php...o&userid=24721
View this thread: http://www.excelforum.com/showthread...hreadid=383303

Run Once Only
 

Hi,

Use the Workbook_Open event of 'ThisWorkbook'.

i.e. put the required code in the 'ThisWorkbook' code sheet in the VBE - e.g.

Private Sub Workbook_Open()
' Insert your required code here ...
End Sub

As it suggests, this code will run only (and automatically) when somebody
opens the workbook.

HTH, Sean.

"gtton" wrote:


Hi, how do I make a macro run one time only? It will not run again
unless I close and open the spreadsheet again.


--
gtton
------------------------------------------------------------------------
gtton's Profile: http://www.excelforum.com/member.php...o&userid=24721
View this thread: http://www.excelforum.com/showthread...hreadid=383303

Run Once Only
 

Thanks for the replies. What if I have multiple macros that I would
like to use only once? What would I name the second, third, etc macros
in ThisWorkbook?


--
gtton
------------------------------------------------------------------------
gtton's Profile: http://www.excelforum.com/member.php...o&userid=24721
View this thread: http://www.excelforum.com/showthread...hreadid=383303

Run Once Only
 

If it is a button then just toggle the enabled property of the button...
Assuming the Button came from the Controls Toolbox and not the forms Toolbar.
The code for the Control toolbox will sit in the sheet. The forms toolbar
code will be in a module that you will have linked the button too.

sub CommandButton1_Click()
'Delete the lines
Sheets.CommandButton1.enabled = false 'Gray out the button
end Sub
--
HTH...

Jim Thomlinson


"gtton" wrote:


Hi Dominic, my macro deletes 4-5 cell rows when you hit a button. I
prefer if the user does not hit the button more than once because they
may delete important information further down the spreadsheet.


--
gtton
------------------------------------------------------------------------
gtton's Profile: http://www.excelforum.com/member.php...o&userid=24721
View this thread: http://www.excelforum.com/showthread...hreadid=383303

Run Once Only
 

Hi Jim, I tried the command button and it won't execute the macro. It
takes me to the Visual Basic Editor


--
gtton
------------------------------------------------------------------------
gtton's Profile: http://www.excelforum.com/member.php...o&userid=24721
View this thread: http://www.excelforum.com/showthread...hreadid=383303

Run Once Only
 

I think the command button is brilliant. But when I try to run a macr
that pastes cells, it runs into error even though on the forms button
it runs fine. It highlights Rows("322:329").Select the line causin
the error in VB editor.

Here's the macro:

Sub CommandButton1_Click()
YesNo = MsgBox("Are You Sure You Want To Insert Multiple Location Dro
Menus?", vbYesNo + 48, "Confirm Multiple Insert")
Select Case YesNo
Case vbYes
Sheets("Data").Select
Rows("322:329").Select
Selection.Copy
Sheet1("3E Submittal Cover Sheet").Select
Rows("46:46").Select
Selection.Insert Shift:=xlDown
ActiveWindow.ScrollRow = 32
Range("B54").Select
Application.ScreenUpdating = False
Case vbNo
'Insert your code here if No is clicked
End Select

Application.ScreenUpdating = True
Sheet1.CommandButton1.Enabled = False 'Gray out the button

End Su

--
gtto
-----------------------------------------------------------------------
gtton's Profile: http://www.excelforum.com/member.php...fo&userid=2472
View this thread: http://www.excelforum.com/showthread.php?threadid=38330

Run Once Only
 

Hi Gtton,

Try changing:

Sheets.CommandButton1.enabled = false 'Gray out the button

to

Me.CommandButton1.Enabled = False 'Gray out the button

---
Regards,
Norman



"gtton" wrote in
message ...

Hi Jim, I tried the command button and it won't execute the macro. It
takes me to the Visual Basic Editor


--
gtton
------------------------------------------------------------------------
gtton's Profile:
http://www.excelforum.com/member.php...o&userid=24721
View this thread: http://www.excelforum.com/showthread...hreadid=383303

Run Once Only
 

Hi Norman, I tried changing to me.commandbutton1.enabled and it stil
doesn't work

--
gtto
-----------------------------------------------------------------------
gtton's Profile: http://www.excelforum.com/member.php...fo&userid=2472
View this thread: http://www.excelforum.com/showthread.php?threadid=38330

Run Once Only
 

Hi Gtton,

I think the command button is brilliant. But when I try to run a macro
that pastes cells, it runs into error even though on the forms button,
it runs fine. It highlights Rows("322:329").Select the line causing
the error in VB editor.

Your code fails on this line because the range is not sufficiently
qualified. If the problem with this line were resolved, your code would fail
on subsequent lines for the same reason.

As your code resides in the worksheet module, any unqualified range
reference will be interpreted as a range on the worksheet holding the code.

Therefore, after your code selects another worksheet [Sheets("Data")], it
sees the line:
Rows("322:329").Select
and, since the range is unqualified, it assumes that this range is on the
sheet holding the code. As your code cannot select a range on (what it
perceives to be) another sheet it throws the error you have experienced. If
you qualify the range thus:

Sheets("Data").Rows("32:39").Select

you will obviate the problem. You will need similarly to qualify other range
references in your code.

it runs into error even though on the forms button, it runs fine.

When you used the Forms button, your code resided in a normal module. Here,
unqualfied range references will be deemed to apply to the active sheet, so
your code worked. In general, it is good (wise?) practice fully to qualify
references as a matter of course. Doing so may appear tedious, but it will
avoid not only the problem you have experienced, but also problems of a much
more subtle nature which can be exceedingly difficult to identify and
resolve.

As a final comment, it is rarely necessary or desirable to make physical
selections. These tend to make your code run more slowly and render your
code more difficult to maintain.

Try therefo

Sub CommandButton1_Click()
Dim YesNo

YesNo = MsgBox("Are You Sure You Want To Insert " _
& "Multiple Location Drop Menus?", _
vbYesNo + 48, "Confirm Multiple Insert")

Select Case YesNo
Case vbYes
Sheets("Data").Rows("32:39").Copy
Sheets("3E Submittal Cover Sheet").Rows("46:46"). _
Insert Shift:=xlDown
ActiveWindow.ScrollRow = 32
Application.Goto Sheets("3E Submittal Cover Sheet"). _
Range("B54")
Application.ScreenUpdating = False
Case vbNo
'Insert your code here if No is clicked
End Select

Application.ScreenUpdating = True

Me.CommandButton1.Enabled = False
End Sub

---
Regards,
Norman



"gtton" wrote in
message ...

I think the command button is brilliant. But when I try to run a macro
that pastes cells, it runs into error even though on the forms button,
it runs fine. It highlights Rows("322:329").Select the line causing
the error in VB editor.

Here's the macro:

Sub CommandButton1_Click()
YesNo = MsgBox("Are You Sure You Want To Insert Multiple Location Drop
Menus?", vbYesNo + 48, "Confirm Multiple Insert")
Select Case YesNo
Case vbYes
Sheets("Data").Select
Rows("322:329").Select
Selection.Copy
Sheet1("3E Submittal Cover Sheet").Select
Rows("46:46").Select
Selection.Insert Shift:=xlDown
ActiveWindow.ScrollRow = 32
Range("B54").Select
Application.ScreenUpdating = False
Case vbNo
'Insert your code here if No is clicked
End Select

Application.ScreenUpdating = True
Sheet1.CommandButton1.Enabled = False 'Gray out the button

End Sub


--
gtton
------------------------------------------------------------------------
gtton's Profile:
http://www.excelforum.com/member.php...o&userid=24721
View this thread: http://www.excelforum.com/showthread...hreadid=383303

Run Once Only
 

Declare a static variable in your code as a boolean. wrap your code i
IF..End If statements like so:


Code
-------------------

Static Already_Run as Boolean

If Not Already_Run then
Already_Run = True
Your Code......
End If

-------------------

--
Rich_
-----------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread.php?threadid=38330

Run Once Only
 

Hi Norman and everyone else who cared enough to view and respond to m
problem, it works! Norman's explaination and solution works t
perfection everytime. Thanks again from the bottom of my heart. :

--
gtto
-----------------------------------------------------------------------
gtton's Profile: http://www.excelforum.com/member.php...fo&userid=2472
View this thread: http://www.excelforum.com/showthread.php?threadid=38330