Clearing an Active X text box with a macro

C

covingj

I have an Active X text box in an Excel workbook that I would like to be able
to clear the contents of the box using a macro. Currently the workbook uses a
macro to clear the contents of cells to prepare it for use. I would like to
add instructions to this exiting macro to also clear the text box being used
to enter notes.

Below is the macro. The workbook has multiple sheets, but the text box only
appear on the BG Info sheet. That section appears at teh end of the Macro.

Thanks for any input.

Sub Clear_All()
'
' Clear_All Macro
' Macro recorded 03/03/2005 by covingj
'
'
Sheets("FI Resources").Select
Range("Clear_FI_Resource").Activate
Selection.ClearContents
Range("C8").Select
ActiveCell.FormulaR1C1 = "1500"
Range("B2:C2").Select
Sheets("PW").Select
Range("Clear_PW").Activate
Selection.ClearContents
Range("F3").Select
Sheets("Preg Minor").Select
Range("Clear_Preg_Minor").Activate
Selection.ClearContents
Range("B7:C7").Select
Sheets("FP").Select
Range("Clear_FP").Activate
Selection.ClearContents
Range("F2").Select
Sheets("LIF").Select
Range("Clear_LIF").Activate
Selection.ClearContents
Range("N2").Select
Sheets("TMA").Select
Range("Clear_TMA").Activate
Selection.ClearContents
Range("F2").Select
Sheets("HCPC").Select
Range("Clear_HCPC").Activate
Selection.ClearContents
Range("F2").Select
Sheets("ABD-SLMB").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_ABD").Activate
Selection.ClearContents
Range("G2").Select
Sheets("QI").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_QI").Activate
Selection.ClearContents
Range("G2").Select
Sheets("OSS").Select
Range("Clear_OSS").Activate
Selection.ClearContents
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("F2").Select
Sheets("NH-HCBS").Select
Range("E7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_NH").Activate
Selection.ClearContents
Range("J28").Select
ActiveCell.FormulaR1C1 = "=R[-6]C"
Range("I2:J2").Select
Sheets("IT").Select
Range("Clear_IT").Activate
Selection.ClearContents
Range("C16").Select
ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
Range("F16").Select
ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
Range("C7").Select
With ActiveCell.Characters(Start:=1, Length:=1)
End With
Range("C7").Select
Sheets("BG Info").Select
Range("Clear_BG_Info").Activate
Selection.ClearContents
Range("Primary").Select
End Sub
 
D

Dave Peterson

If you know the name of the textbox:
Worksheets("BG Info").TextBox1.Value = ""

ps.

This code:
Sheets("FI Resources").Select
Range("Clear_FI_Resource").Activate
Selection.ClearContents
Range("C8").Select
ActiveCell.FormulaR1C1 = "1500"
Click to expand...

could be rewritten as:

Sheets("FI Resources").Range("Clear_FI_Resource").clearcontents
sheets("FI Resources").Range("C8").value = 1500

This kind of thing will make the code run a bit faster, but even better, it's
easier to understand.
I have an Active X text box in an Excel workbook that I would like to be able
to clear the contents of the box using a macro. Currently the workbook uses a
macro to clear the contents of cells to prepare it for use. I would like to
add instructions to this exiting macro to also clear the text box being used
to enter notes.

Below is the macro. The workbook has multiple sheets, but the text box only
appear on the BG Info sheet. That section appears at teh end of the Macro.

Thanks for any input.

Sub Clear_All()
'
' Clear_All Macro
' Macro recorded 03/03/2005 by covingj
'
'
Sheets("FI Resources").Select
Range("Clear_FI_Resource").Activate
Selection.ClearContents
Range("C8").Select
ActiveCell.FormulaR1C1 = "1500"
Range("B2:C2").Select
Sheets("PW").Select
Range("Clear_PW").Activate
Selection.ClearContents
Range("F3").Select
Sheets("Preg Minor").Select
Range("Clear_Preg_Minor").Activate
Selection.ClearContents
Range("B7:C7").Select
Sheets("FP").Select
Range("Clear_FP").Activate
Selection.ClearContents
Range("F2").Select
Sheets("LIF").Select
Range("Clear_LIF").Activate
Selection.ClearContents
Range("N2").Select
Sheets("TMA").Select
Range("Clear_TMA").Activate
Selection.ClearContents
Range("F2").Select
Sheets("HCPC").Select
Range("Clear_HCPC").Activate
Selection.ClearContents
Range("F2").Select
Sheets("ABD-SLMB").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_ABD").Activate
Selection.ClearContents
Range("G2").Select
Sheets("QI").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_QI").Activate
Selection.ClearContents
Range("G2").Select
Sheets("OSS").Select
Range("Clear_OSS").Activate
Selection.ClearContents
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("F2").Select
Sheets("NH-HCBS").Select
Range("E7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_NH").Activate
Selection.ClearContents
Range("J28").Select
ActiveCell.FormulaR1C1 = "=R[-6]C"
Range("I2:J2").Select
Sheets("IT").Select
Range("Clear_IT").Activate
Selection.ClearContents
Range("C16").Select
ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
Range("F16").Select
ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
Range("C7").Select
With ActiveCell.Characters(Start:=1, Length:=1)
End With
Range("C7").Select
Sheets("BG Info").Select
Range("Clear_BG_Info").Activate
Selection.ClearContents
Range("Primary").Select
End Sub
Click to expand...
 
C

covingj

Thanks, that worked like a charm.

As far as the code, that's what you get when you let Microsoft record a
macro for you. It is indented to make it a little easier on the eyes, but the
formating did not carry over when I pasted. When I have time, I'll try to
clean it up like you suggested. Of course it would help if I understood what
I was doing and not just making it up as I went, LOL.

Thanks again.

Dave Peterson said:
If you know the name of the textbox:
Worksheets("BG Info").TextBox1.Value = ""

ps.

This code:
Sheets("FI Resources").Select
Range("Clear_FI_Resource").Activate
Selection.ClearContents
Range("C8").Select
ActiveCell.FormulaR1C1 = "1500"
Click to expand...

could be rewritten as:

Sheets("FI Resources").Range("Clear_FI_Resource").clearcontents
sheets("FI Resources").Range("C8").value = 1500

This kind of thing will make the code run a bit faster, but even better, it's
easier to understand.
I have an Active X text box in an Excel workbook that I would like to be able
to clear the contents of the box using a macro. Currently the workbook uses a
macro to clear the contents of cells to prepare it for use. I would like to
add instructions to this exiting macro to also clear the text box being used
to enter notes.

Below is the macro. The workbook has multiple sheets, but the text box only
appear on the BG Info sheet. That section appears at teh end of the Macro.

Thanks for any input.

Sub Clear_All()
'
' Clear_All Macro
' Macro recorded 03/03/2005 by covingj
'
'
Sheets("FI Resources").Select
Range("Clear_FI_Resource").Activate
Selection.ClearContents
Range("C8").Select
ActiveCell.FormulaR1C1 = "1500"
Range("B2:C2").Select
Sheets("PW").Select
Range("Clear_PW").Activate
Selection.ClearContents
Range("F3").Select
Sheets("Preg Minor").Select
Range("Clear_Preg_Minor").Activate
Selection.ClearContents
Range("B7:C7").Select
Sheets("FP").Select
Range("Clear_FP").Activate
Selection.ClearContents
Range("F2").Select
Sheets("LIF").Select
Range("Clear_LIF").Activate
Selection.ClearContents
Range("N2").Select
Sheets("TMA").Select
Range("Clear_TMA").Activate
Selection.ClearContents
Range("F2").Select
Sheets("HCPC").Select
Range("Clear_HCPC").Activate
Selection.ClearContents
Range("F2").Select
Sheets("ABD-SLMB").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_ABD").Activate
Selection.ClearContents
Range("G2").Select
Sheets("QI").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_QI").Activate
Selection.ClearContents
Range("G2").Select
Sheets("OSS").Select
Range("Clear_OSS").Activate
Selection.ClearContents
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("F2").Select
Sheets("NH-HCBS").Select
Range("E7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_NH").Activate
Selection.ClearContents
Range("J28").Select
ActiveCell.FormulaR1C1 = "=R[-6]C"
Range("I2:J2").Select
Sheets("IT").Select
Range("Clear_IT").Activate
Selection.ClearContents
Range("C16").Select
ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
Range("F16").Select
ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
Range("C7").Select
With ActiveCell.Characters(Start:=1, Length:=1)
End With
Range("C7").Select
Sheets("BG Info").Select
Range("Clear_BG_Info").Activate
Selection.ClearContents
Range("Primary").Select
End Sub
Click to expand...
Click to expand...
 
R

Rick Rothstein

The problem with the Recorder is it must encode each action you take one at
a time... it has no facilities to optimize the code after it has done this.
Perhaps this previous posting of mine will help you when you do get around
to cleaning your code up:

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)


covingj said:
Thanks, that worked like a charm.

As far as the code, that's what you get when you let Microsoft record a
macro for you. It is indented to make it a little easier on the eyes, but
the
formating did not carry over when I pasted. When I have time, I'll try to
clean it up like you suggested. Of course it would help if I understood
what
I was doing and not just making it up as I went, LOL.

Thanks again.

Dave Peterson said:
If you know the name of the textbox:
Worksheets("BG Info").TextBox1.Value = ""

ps.

This code:
Sheets("FI Resources").Select
Range("Clear_FI_Resource").Activate
Selection.ClearContents
Range("C8").Select
ActiveCell.FormulaR1C1 = "1500"
Click to expand...

could be rewritten as:

Sheets("FI Resources").Range("Clear_FI_Resource").clearcontents
sheets("FI Resources").Range("C8").value = 1500

This kind of thing will make the code run a bit faster, but even better,
it's
easier to understand.
I have an Active X text box in an Excel workbook that I would like to
be able
to clear the contents of the box using a macro. Currently the workbook
uses a
macro to clear the contents of cells to prepare it for use. I would
like to
add instructions to this exiting macro to also clear the text box being
used
to enter notes.

Below is the macro. The workbook has multiple sheets, but the text box
only
appear on the BG Info sheet. That section appears at teh end of the
Macro.

Thanks for any input.

Sub Clear_All()
'
' Clear_All Macro
' Macro recorded 03/03/2005 by covingj
'
'
Sheets("FI Resources").Select
Range("Clear_FI_Resource").Activate
Selection.ClearContents
Range("C8").Select
ActiveCell.FormulaR1C1 = "1500"
Range("B2:C2").Select
Sheets("PW").Select
Range("Clear_PW").Activate
Selection.ClearContents
Range("F3").Select
Sheets("Preg Minor").Select
Range("Clear_Preg_Minor").Activate
Selection.ClearContents
Range("B7:C7").Select
Sheets("FP").Select
Range("Clear_FP").Activate
Selection.ClearContents
Range("F2").Select
Sheets("LIF").Select
Range("Clear_LIF").Activate
Selection.ClearContents
Range("N2").Select
Sheets("TMA").Select
Range("Clear_TMA").Activate
Selection.ClearContents
Range("F2").Select
Sheets("HCPC").Select
Range("Clear_HCPC").Activate
Selection.ClearContents
Range("F2").Select
Sheets("ABD-SLMB").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_ABD").Activate
Selection.ClearContents
Range("G2").Select
Sheets("QI").Select
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_QI").Activate
Selection.ClearContents
Range("G2").Select
Sheets("OSS").Select
Range("Clear_OSS").Activate
Selection.ClearContents
Range("C7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("F2").Select
Sheets("NH-HCBS").Select
Range("E7").Select
ActiveCell.FormulaR1C1 = "1500"
Range("Clear_NH").Activate
Selection.ClearContents
Range("J28").Select
ActiveCell.FormulaR1C1 = "=R[-6]C"
Range("I2:J2").Select
Sheets("IT").Select
Range("Clear_IT").Activate
Selection.ClearContents
Range("C16").Select
ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
Range("F16").Select
ActiveCell.FormulaR1C1 = "='NH-HCBS'!R22C10"
Range("C7").Select
With ActiveCell.Characters(Start:=1, Length:=1)
End With
Range("C7").Select
Sheets("BG Info").Select
Range("Clear_BG_Info").Activate
Selection.ClearContents
Range("Primary").Select
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with error 53 8
macro 2
Advanced Filter works in recorder but not while running macro 5
How can I indicate in a macro to which last sheet to return ? 9
macro to copy rows that contain a cell with a certain number 3
Including an inputbox within a macro 1
How do I direct a macro to a newly opened workbook 2
Clearing data from a so called protected sheet??? 5

Top