Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Please help: Running a macro only once
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Please help: Running a macro only once
I'm trying to get the following macro to run only once --
I've assigned the macro to a button, but I'm afraid
someone will accidently click the button twice:

Sub PA_ONLY()
Columns("H:M").Select
Range("H2").Activate
Selection.NumberFormat = "m/d/yy"
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="-1"
Range("C6:D2000").Select
Selection.ClearContents
Range("G6:M2000").Select
Selection.ClearContents
Rows("6:2000").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("A6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[5],10,2)"
Range("A6").Select
Selection.Copy
Range("A6:A2000").Select
ActiveSheet.Paste
Range("C5").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=3, Criteria1:="0",
Operator:=xlAnd
Range("A7").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[1]=RC[1],R[-1]
C,"""")"
Range("A7").Select
Selection.Copy
Range("A7:A2000").Select
ActiveSheet.Paste
Range("C5").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=3
Range("A6:A2000").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.AutoFilter
Rows("5:5").Select
Selection.Delete Shift:=xlUp
Columns("N:O").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.AddIndent = False
.ShrinkToFit = False
End With
Columns("F:F").Select
Range("F2").Activate
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
End With
Range("G2:G4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 90
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range("D2:D4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 90
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range("A2:A4").Select
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Please help: Running a macro only once
assuming the sheet will be pristine in terms of number format prior to
running the macro.

Sub PA_ONLY()
Columns("H:M").Select
Range("H2").Activate
if ActiveCell.Numberformat = "m/d/yy" then exit sub
Selection.NumberFormat = "m/d/yy"
Rows("5:5").Select

Regards,
Tom Ogilvy


"Brooks" wrote in message
...
I'm trying to get the following macro to run only once --
I've assigned the macro to a button, but I'm afraid
someone will accidently click the button twice:

Sub PA_ONLY()
Columns("H:M").Select
Range("H2").Activate
Selection.NumberFormat = "m/d/yy"
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="-1"
Range("C6:D2000").Select
Selection.ClearContents
Range("G6:M2000").Select
Selection.ClearContents
Rows("6:2000").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("A6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[5],10,2)"
Range("A6").Select
Selection.Copy
Range("A6:A2000").Select
ActiveSheet.Paste
Range("C5").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=3, Criteria1:="0",
Operator:=xlAnd
Range("A7").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[1]=RC[1],R[-1]
C,"""")"
Range("A7").Select
Selection.Copy
Range("A7:A2000").Select
ActiveSheet.Paste
Range("C5").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=3
Range("A6:A2000").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.AutoFilter
Rows("5:5").Select
Selection.Delete Shift:=xlUp
Columns("N:O").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.AddIndent = False
.ShrinkToFit = False
End With
Columns("F:F").Select
Range("F2").Activate
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
End With
Range("G2:G4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 90
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range("D2:D4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 90
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range("A2:A4").Select
End Sub



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Please help: Running a macro only once
If I understand you correct
and you only want to use the macro one time.

Place your macro in a module called Module1 together
with the macro deletemodule and run the sub deletemodule.

The whole Module will be deleted, remember that.


Sub deletemodule()
Yourmacro
With ThisWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
End With
End Sub

Sub Yourmacro()
MsgBox "Hi"
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Brooks" wrote in message ...
I'm trying to get the following macro to run only once --
I've assigned the macro to a button, but I'm afraid
someone will accidently click the button twice:

Sub PA_ONLY()
Columns("H:M").Select
Range("H2").Activate
Selection.NumberFormat = "m/d/yy"
Rows("5:5").Select
Selection.Insert Shift:=xlDown
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="-1"
Range("C6:D2000").Select
Selection.ClearContents
Range("G6:M2000").Select
Selection.ClearContents
Rows("6:2000").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("A6").Select
ActiveCell.FormulaR1C1 = "=MID(RC[5],10,2)"
Range("A6").Select
Selection.Copy
Range("A6:A2000").Select
ActiveSheet.Paste
Range("C5").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=3, Criteria1:="0",
Operator:=xlAnd
Range("A7").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[1]=RC[1],R[-1]
C,"""")"
Range("A7").Select
Selection.Copy
Range("A7:A2000").Select
ActiveSheet.Paste
Range("C5").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=3
Range("A6:A2000").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.AutoFilter
Rows("5:5").Select
Selection.Delete Shift:=xlUp
Columns("N:O").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.AddIndent = False
.ShrinkToFit = False
End With
Columns("F:F").Select
Range("F2").Activate
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
End With
Range("G2:G4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 90
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range("D2:D4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 90
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range("A2:A4").Select
End Sub



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Disable running of SelectionChange macro when in another macro? Tonso Excel Discussion (Misc queries) 6 March 21st 10 06:50 PM
Pop-up when running a macro tom Excel Discussion (Misc queries) 5 April 2nd 07 08:55 AM
Event Macro running another macro inside K1KKKA Excel Discussion (Misc queries) 1 December 20th 06 08:21 PM
disable user running macro from Tools Macro Steve Simons Excel Discussion (Misc queries) 4 September 28th 06 06:28 AM
Running VBA Macro Jeff Excel Discussion (Misc queries) 1 February 15th 05 01:12 PM


All times are GMT +1. The time now is 03:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement