| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a proprer macro in Excel
sorry for my ignorance. However, below is what I have so far....where would
you suggest putting the code below and do I need to replace any of the words in the code below with a reference to my existing code? Sub ClearContents() ' ' ClearContents Macro ' Clear all Fields in Table ' ' Range("I6:P6").Select Selection.ClearContents Range("I7:P7").Select Selection.ClearContents Range("I9:P9").Select Selection.ClearContents Range("I10:P10").Select Selection.ClearContents Range("I11:P11").Select Selection.ClearContents Range("I12:P12").Select Selection.ClearContents Range("I13:P13").Select Selection.ClearContents Range("U6:X6").Select Selection.ClearContents Range("U7:X7").Select Selection.ClearContents Range("U52:X52").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=12 Range("Q55:T55").Select Selection.ClearContents End Sub "Mike H" wrote in message ... Maybe this Sub clearrange() msg = "Are you sure you want to clear range?" response = MsgBox(msg, vbOKCancel) If response = vbOK Then 'Do your stuff MsgBox "You pressed OK" Else MsgBox "You pressed cancel" Exit Sub End If End Sub Mike "Derek Upson - Pioneer" wrote: I have a spreadsheet model. Within the model are a bunch of fields requiring input. I wanted to have a macro that would clear all of these fields so someone could start over. That was the easy part. However, I don't want someone to click on the button that starts the macro by accident (wiping out everything already done). Therefore, I want to add a pop-up box to the beginning of the macro that asks the user to verify that they want all contents cleared. Can someone help me understand how this can be added to the macro? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a proprer macro in Excel
hi
the select statment is not needed in this case so it can be iliminated. also the small scroll is not needed. in coding the scroll and select are only good if you are watching the macro to stuff in design mode. in run mode usually, things are happening too fast. Range("I6:P6").ClearContents Range("I7:P7").ClearContents Range("I9:P9").ClearContents Range("I10:P10").ClearContents Range("I11:P11").ClearContents Range("I12:P12").ClearContents Range("I13:P13").ClearContents Range("U6:X6").ClearContents Range("U7:X7").ClearContents Range("U52:X52").ClearContents Range("Q55:T55").ClearContents regards FSt1 "Derek Upson - Pioneer" wrote: sorry for my ignorance. However, below is what I have so far....where would you suggest putting the code below and do I need to replace any of the words in the code below with a reference to my existing code? Sub ClearContents() ' ' ClearContents Macro ' Clear all Fields in Table ' ' Range("I6:P6").Select Selection.ClearContents Range("I7:P7").Select Selection.ClearContents Range("I9:P9").Select Selection.ClearContents Range("I10:P10").Select Selection.ClearContents Range("I11:P11").Select Selection.ClearContents Range("I12:P12").Select Selection.ClearContents Range("I13:P13").Select Selection.ClearContents Range("U6:X6").Select Selection.ClearContents Range("U7:X7").Select Selection.ClearContents Range("U52:X52").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=12 Range("Q55:T55").Select Selection.ClearContents End Sub "Mike H" wrote in message ... Maybe this Sub clearrange() msg = "Are you sure you want to clear range?" response = MsgBox(msg, vbOKCancel) If response = vbOK Then 'Do your stuff MsgBox "You pressed OK" Else MsgBox "You pressed cancel" Exit Sub End If End Sub Mike "Derek Upson - Pioneer" wrote: I have a spreadsheet model. Within the model are a bunch of fields requiring input. I wanted to have a macro that would clear all of these fields so someone could start over. That was the easy part. However, I don't want someone to click on the button that starts the macro by accident (wiping out everything already done). Therefore, I want to add a pop-up box to the beginning of the macro that asks the user to verify that they want all contents cleared. Can someone help me understand how this can be added to the macro? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a proprer macro in Excel
Thanks FSt1. I will make those changes right now (great suggestions and I
understand them completely). However, what code would I add if I want a pop-up box to verify the clearcontents before it is actually done. I am trying to prevent someone from wiping out work accidentally that took 2 hours to input. "FSt1" wrote in message ... hi the select statment is not needed in this case so it can be iliminated. also the small scroll is not needed. in coding the scroll and select are only good if you are watching the macro to stuff in design mode. in run mode usually, things are happening too fast. Range("I6:P6").ClearContents Range("I7:P7").ClearContents Range("I9:P9").ClearContents Range("I10:P10").ClearContents Range("I11:P11").ClearContents Range("I12:P12").ClearContents Range("I13:P13").ClearContents Range("U6:X6").ClearContents Range("U7:X7").ClearContents Range("U52:X52").ClearContents Range("Q55:T55").ClearContents regards FSt1 "Derek Upson - Pioneer" wrote: sorry for my ignorance. However, below is what I have so far....where would you suggest putting the code below and do I need to replace any of the words in the code below with a reference to my existing code? Sub ClearContents() ' ' ClearContents Macro ' Clear all Fields in Table ' ' Range("I6:P6").Select Selection.ClearContents Range("I7:P7").Select Selection.ClearContents Range("I9:P9").Select Selection.ClearContents Range("I10:P10").Select Selection.ClearContents Range("I11:P11").Select Selection.ClearContents Range("I12:P12").Select Selection.ClearContents Range("I13:P13").Select Selection.ClearContents Range("U6:X6").Select Selection.ClearContents Range("U7:X7").Select Selection.ClearContents Range("U52:X52").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=12 Range("Q55:T55").Select Selection.ClearContents End Sub "Mike H" wrote in message ... Maybe this Sub clearrange() msg = "Are you sure you want to clear range?" response = MsgBox(msg, vbOKCancel) If response = vbOK Then 'Do your stuff MsgBox "You pressed OK" Else MsgBox "You pressed cancel" Exit Sub End If End Sub Mike "Derek Upson - Pioneer" wrote: I have a spreadsheet model. Within the model are a bunch of fields requiring input. I wanted to have a macro that would clear all of these fields so someone could start over. That was the easy part. However, I don't want someone to click on the button that starts the macro by accident (wiping out everything already done). Therefore, I want to add a pop-up box to the beginning of the macro that asks the user to verify that they want all contents cleared. Can someone help me understand how this can be added to the macro? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a proprer macro in Excel
You could condense it down even further like this
Range("I6:P7").ClearContents Range("I9:P13").ClearContents Range("U6:X7").ClearContents Range("U52:X52").ClearContents Range("Q55:T55").ClearContents --JP On Jan 15, 3:48*pm, FSt1 wrote: hi the select statment is not needed in this case so it can be iliminated. also the small scroll is not needed. in coding *the scroll and select are only good if you are watching the macro to stuff in design mode. in run mode usually, things are happening too fast. Range("I6:P6").ClearContents * * Range("I7:P7").ClearContents * * Range("I9:P9").ClearContents * * Range("I10:P10").ClearContents * * Range("I11:P11").ClearContents * * Range("I12:P12").ClearContents * * Range("I13:P13").ClearContents * * Range("U6:X6").ClearContents * * Range("U7:X7").ClearContents * * Range("U52:X52").ClearContents * * Range("Q55:T55").ClearContents regards FSt1 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a proprer macro in Excel
And even further like this..........
For Each rcell In _ ActiveSheet.Range("I6:P7, I9:P13, U6:X7, U52:X52, Q55:T55") rcell.ClearContents Gord On Tue, 15 Jan 2008 14:08:14 -0800 (PST), JP wrote: You could condense it down even further like this Range("I6:P7").ClearContents Range("I9:P13").ClearContents Range("U6:X7").ClearContents Range("U52:X52").ClearContents Range("Q55:T55").ClearContents --JP On Jan 15, 3:48*pm, FSt1 wrote: hi the select statment is not needed in this case so it can be iliminated. also the small scroll is not needed. in coding *the scroll and select are only good if you are watching the macro to stuff in design mode. in run mode usually, things are happening too fast. Range("I6:P6").ClearContents * * Range("I7:P7").ClearContents * * Range("I9:P9").ClearContents * * Range("I10:P10").ClearContents * * Range("I11:P11").ClearContents * * Range("I12:P12").ClearContents * * Range("I13:P13").ClearContents * * Range("U6:X6").ClearContents * * Range("U7:X7").ClearContents * * Range("U52:X52").ClearContents * * Range("Q55:T55").ClearContents regards FSt1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a proprer macro in Excel
The macro recorder adds a great bunch of "Selects" you don't need.
Sub ClearContents() Dim rcell as Range msg = "Are you sure you want to clear range?" response = MsgBox(msg, vbOKCancel) If response = vbOK Then MsgBox "You pressed OK. Cells will be cleared" For Each rcell In _ ActiveSheet.Range("I6:P7, I9:P13, U6:X7, U52:X52, Q55:T55") rcell.ClearContents Next Else MsgBox "You pressed cancel. Nothing changed" Exit Sub End If End Sub Gord Dibben MS Excel MVP On Tue, 15 Jan 2008 15:18:35 -0500, "Derek Upson - Pioneer" wrote: sorry for my ignorance. However, below is what I have so far....where would you suggest putting the code below and do I need to replace any of the words in the code below with a reference to my existing code? Sub ClearContents() ' ' ClearContents Macro ' Clear all Fields in Table ' ' Range("I6:P6").Select Selection.ClearContents Range("I7:P7").Select Selection.ClearContents Range("I9:P9").Select Selection.ClearContents Range("I10:P10").Select Selection.ClearContents Range("I11:P11").Select Selection.ClearContents Range("I12:P12").Select Selection.ClearContents Range("I13:P13").Select Selection.ClearContents Range("U6:X6").Select Selection.ClearContents Range("U7:X7").Select Selection.ClearContents Range("U52:X52").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=12 Range("Q55:T55").Select Selection.ClearContents End Sub "Mike H" wrote in message ... Maybe this Sub clearrange() msg = "Are you sure you want to clear range?" response = MsgBox(msg, vbOKCancel) If response = vbOK Then 'Do your stuff MsgBox "You pressed OK" Else MsgBox "You pressed cancel" Exit Sub End If End Sub Mike "Derek Upson - Pioneer" wrote: I have a spreadsheet model. Within the model are a bunch of fields requiring input. I wanted to have a macro that would clear all of these fields so someone could start over. That was the easy part. However, I don't want someone to click on the button that starts the macro by accident (wiping out everything already done). Therefore, I want to add a pop-up box to the beginning of the macro that asks the user to verify that they want all contents cleared. Can someone help me understand how this can be added to the macro? Thanks in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a proprer macro in Excel
thanks for the first attempt. It looks like this is a problem since some of
the cells are partially merged? it is giving me a problem. Any suggestions? "Gord Dibben" <gorddibbATshawDOTca wrote in message ... The macro recorder adds a great bunch of "Selects" you don't need. Sub ClearContents() Dim rcell as Range msg = "Are you sure you want to clear range?" response = MsgBox(msg, vbOKCancel) If response = vbOK Then MsgBox "You pressed OK. Cells will be cleared" For Each rcell In _ ActiveSheet.Range("I6:P7, I9:P13, U6:X7, U52:X52, Q55:T55") rcell.ClearContents Next Else MsgBox "You pressed cancel. Nothing changed" Exit Sub End If End Sub Gord Dibben MS Excel MVP On Tue, 15 Jan 2008 15:18:35 -0500, "Derek Upson - Pioneer" wrote: sorry for my ignorance. However, below is what I have so far....where would you suggest putting the code below and do I need to replace any of the words in the code below with a reference to my existing code? Sub ClearContents() ' ' ClearContents Macro ' Clear all Fields in Table ' ' Range("I6:P6").Select Selection.ClearContents Range("I7:P7").Select Selection.ClearContents Range("I9:P9").Select Selection.ClearContents Range("I10:P10").Select Selection.ClearContents Range("I11:P11").Select Selection.ClearContents Range("I12:P12").Select Selection.ClearContents Range("I13:P13").Select Selection.ClearContents Range("U6:X6").Select Selection.ClearContents Range("U7:X7").Select Selection.ClearContents Range("U52:X52").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=12 Range("Q55:T55").Select Selection.ClearContents End Sub "Mike H" wrote in message ... Maybe this Sub clearrange() msg = "Are you sure you want to clear range?" response = MsgBox(msg, vbOKCancel) If response = vbOK Then 'Do your stuff MsgBox "You pressed OK" Else MsgBox "You pressed cancel" Exit Sub End If End Sub Mike "Derek Upson - Pioneer" wrote: I have a spreadsheet model. Within the model are a bunch of fields requiring input. I wanted to have a macro that would clear all of these fields so someone could start over. That was the easy part. However, I don't want someone to click on the button that starts the macro by accident (wiping out everything already done). Therefore, I want to add a pop-up box to the beginning of the macro that asks the user to verify that they want all contents cleared. Can someone help me understand how this can be added to the macro? Thanks in advance. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a proprer macro in Excel
What is a "parially" merged cell?
Cells are merged or not. Suggestions...................GET RID OF MERGED CELLS! Note the caps which denotes shouting really loudly. Gord On Tue, 15 Jan 2008 18:09:19 -0500, "Derek Upson - Pioneer" wrote: thanks for the first attempt. It looks like this is a problem since some of the cells are partially merged? it is giving me a problem. Any suggestions? "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . The macro recorder adds a great bunch of "Selects" you don't need. Sub ClearContents() Dim rcell as Range msg = "Are you sure you want to clear range?" response = MsgBox(msg, vbOKCancel) If response = vbOK Then MsgBox "You pressed OK. Cells will be cleared" For Each rcell In _ ActiveSheet.Range("I6:P7, I9:P13, U6:X7, U52:X52, Q55:T55") rcell.ClearContents Next Else MsgBox "You pressed cancel. Nothing changed" Exit Sub End If End Sub Gord Dibben MS Excel MVP On Tue, 15 Jan 2008 15:18:35 -0500, "Derek Upson - Pioneer" wrote: sorry for my ignorance. However, below is what I have so far....where would you suggest putting the code below and do I need to replace any of the words in the code below with a reference to my existing code? Sub ClearContents() ' ' ClearContents Macro ' Clear all Fields in Table ' ' Range("I6:P6").Select Selection.ClearContents Range("I7:P7").Select Selection.ClearContents Range("I9:P9").Select Selection.ClearContents Range("I10:P10").Select Selection.ClearContents Range("I11:P11").Select Selection.ClearContents Range("I12:P12").Select Selection.ClearContents Range("I13:P13").Select Selection.ClearContents Range("U6:X6").Select Selection.ClearContents Range("U7:X7").Select Selection.ClearContents Range("U52:X52").Select Selection.ClearContents ActiveWindow.SmallScroll Down:=12 Range("Q55:T55").Select Selection.ClearContents End Sub "Mike H" wrote in message ... Maybe this Sub clearrange() msg = "Are you sure you want to clear range?" response = MsgBox(msg, vbOKCancel) If response = vbOK Then 'Do your stuff MsgBox "You pressed OK" Else MsgBox "You pressed cancel" Exit Sub End If End Sub Mike "Derek Upson - Pioneer" wrote: I have a spreadsheet model. Within the model are a bunch of fields requiring input. I wanted to have a macro that would clear all of these fields so someone could start over. That was the easy part. However, I don't want someone to click on the button that starts the macro by accident (wiping out everything already done). Therefore, I want to add a pop-up box to the beginning of the macro that asks the user to verify that they want all contents cleared. Can someone help me understand how this can be added to the macro? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating Macro | Excel Discussion (Misc queries) | |||
| Advice on Creating an Excel Formula or Macro - PLEASE HELP!!!! | Excel Worksheet Functions | |||
| help creating a macro in excel that opens a specific word document | Excel Discussion (Misc queries) | |||
| Creating a macro in excel | Excel Worksheet Functions | |||
| after creating macro button, closed excel then restarted excel | Excel Discussion (Misc queries) | |||
Hybrid Mode
