Pasting TextBox values

[Patrick Simonds]

In the code below I am attempting to select all of my worksheets, then
select cell A1301 and past the data from textboxes into cell A1301 and B1301
of all the Worksheets. What happens is that all of the worksheets are
selected and cell A1301 is selected on all the worksheets but the data is
only placed on the 1st worksheet (June - August). So two questions:

1. Is there a shorter way to select all worksheets?
2. How can I paste the TextBox values to all of the worksheets?


Dim rng
Set rng = Cells(ActiveCell.Row, 1)

Sheets(Array("June - August", "September - November", "December -
February", _
"March - May", "Annual")).Select
Sheets("June - August").Activate

Range("A1301").Select

rng(1, 1).Value = TextBox3.Text
rng(1, 2).Value = TextBox5.Text

 

[Norman Jones]

Hi Patrick,

1. Is there a shorter way to select all worksheets?

Yes: Sheets.Select.

Unfortunately, unlike in Excel, VBA requires each sheet to be addressed
separately.

Try, therefore, something like:

'=================>>
Sub Test03()
Dim SH As Worksheet
Dim rng As Range
Const strAdd As String = "A1301"

For Each SH In ActiveWorkbook.Worksheets
Set rng = SH.Range(strAdd)
rng(1, 1).Value = TextBox3.Text
rng(1, 2).Value = TextBox5.Text
Next SH

End Sub
'<<=================

 

[Patrick Simonds]

Thank you that worked great. So of course it leads to one more question:

How can I get it to return me to the worksheet that was active when the code
was run?

 

[Norman Jones]

Hi Patrick,

How can I get it to return me to the worksheet that was active when the
code was run?

The suggested code makes no selections. Therefore, the active sheet remains
unchanged by the macro

 

[Gary Keramidas]

one way

Worksheets("sheet3").Select

--


Gary

Thank you that worked great. So of course it leads to one more question:

How can I get it to return me to the worksheet that was active when the
code was run?

 

[Gary Keramidas]

norman:

forgot i was going to say that in my reply, but was going to add the op must
have code selecting other sheets elsewhere.

 

[Norman Jones]

Hi Patrick,

the op must have code selecting other sheets elsewhere.

If, as Gary surmises, sheets are being selected elsewhere in your code, then
I would suggest that you rewrite the code to eliminate such selections:
these are rarely necessary and are ususually inefficient.

If, for any reason, sheet selections are unavoidable, try the following
approach:

'=================>>
Public Sub AAA()
Dim theSheet As Worksheet
Dim theRng As Range
Dim theCell As Range

Set theSheet = ActiveSheet
Set theRange = Selection
Set theCell = ActiveCell

'Your code

theSheet.Activate
theRng.Select
theCell.Activate

End Sub
'<<=================

 

[Patrick Simonds]

Other sheets are being select for the purposes of protecting/unprotecting
them.:

ActiveSheet.Unprotect
Sheets("September - November").Select
ActiveSheet.Unprotect
Sheets("December - February").Select
ActiveSheet.Unprotect
Sheets("March - May").Select
ActiveSheet.Unprotect
Sheets("Annual").Select
ActiveSheet.Unprotect

Could not come up with a way to protect/unprotect them without them being
active.

 

[Patrick Simonds]

Got it. A was able to modify Mr. Jones code to read:

Dim SH As Worksheet

For Each SH In ActiveWorkbook.Worksheets

SH.Unprotect

Next SH

Thank you for all the assistance

 

[Gary Keramidas]

the same way, patrick


For Each Sheet In ThisWorkbook.Sheets
Sheet.Protect
Next


or

For Each Sheet In ThisWorkbook.Sheets
Sheet.Unprotect
Next

 

[Norman Jones]

Hi Patrick,

Try:
'=================>>
Sub Tester04()
Dim SH As Worksheet

For Each SH In ActiveWorkbook.Worksheets
SH.Protect
Next SH

End Sub
'<<=================

Alternatively, to protect specific sheets, try:

'=================>>
Sub Tester05()
Dim SH As Worksheet


For Each SH In Sheets(Array("June - August", _
"September - November", _
"December-February", _
"March - May", "Annual"))
SH.Protect
Next SH

End Sub
'<<=================

In each case protection is effected without selection.