ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Creating a proprer macro in Excel (https://www.excelbanter.com/excel-worksheet-functions/173162-creating-proprer-macro-excel.html)

Derek Upson - Pioneer
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.

FSt1
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.

Derek Upson - Pioneer
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.

Gord Dibben
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.

JP[_4_]
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

Derek Upson - Pioneer
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.

Gord Dibben
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.

Gord Dibben
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


All times are GMT +1. The time now is 11:25 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com