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

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

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

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

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

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

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

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

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

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