Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Clearing an Active X text box with a macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Clearing an Active X text box with a macro

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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Clearing an Active X text box with a macro

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"

could be rewritten as:

Sheets("FI Resources").Range("Clear_FI_Resource").clearconten ts
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.

covingj wrote:

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

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Clearing an Active X text box with a macro
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" wrote:

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"

could be rewritten as:

Sheets("FI Resources").Range("Clear_FI_Resource").clearconten ts
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.

covingj wrote:

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

--

Dave Peterson

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Clearing an Active X text box with a macro
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" wrote in message
...
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" wrote:

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"

could be rewritten as:

Sheets("FI Resources").Range("Clear_FI_Resource").clearconten ts
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.

covingj wrote:

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

--

Dave Peterson


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
macro for clearing cells mocc Excel Discussion (Misc queries) 11 September 19th 09 10:43 PM
run macro although blinking cursor is active in an active cell bartman1980 Excel Programming 1 December 20th 07 11:29 AM
Clearing text in TextBoxes CG Rosén Excel Programming 3 August 18th 05 10:01 PM
clearing text from text boxes Jae Excel Discussion (Misc queries) 5 August 18th 05 09:10 PM
Clearing all Active Cells JohnHill Excel Worksheet Functions 2 July 26th 05 06:30 AM


All times are GMT +1. The time now is 08:37 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