| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pasting TextBox values
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
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" wrote in message ... 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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
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 --- Regards, Norman "Patrick Simonds" wrote in message ... 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" wrote in message ... 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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
one way
Worksheets("sheet3").Select -- Gary "Patrick Simonds" wrote in message ... 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" wrote in message ... 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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
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. -- Gary "Norman Jones" wrote in message ... 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 --- Regards, Norman "Patrick Simonds" wrote in message ... 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" wrote in message ... 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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
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 '<<================= --- Regards, Norman "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... 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. -- Gary "Norman Jones" wrote in message ... 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 --- Regards, Norman "Patrick Simonds" wrote in message ... 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" wrote in message ... 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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
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. "Norman Jones" wrote in message ... 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 '<<================= --- Regards, Norman "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... 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. -- Gary "Norman Jones" wrote in message ... 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 --- Regards, Norman "Patrick Simonds" wrote in message ... 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" wrote in message ... 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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
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 "Patrick Simonds" wrote in message ... 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. "Norman Jones" wrote in message ... 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 '<<================= --- Regards, Norman "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... 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. -- Gary "Norman Jones" wrote in message ... 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 --- Regards, Norman "Patrick Simonds" wrote in message ... 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" wrote in message ... 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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
the same way, patrick
For Each Sheet In ThisWorkbook.Sheets Sheet.Protect Next or For Each Sheet In ThisWorkbook.Sheets Sheet.Unprotect Next -- Gary "Patrick Simonds" wrote in message ... 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. "Norman Jones" wrote in message ... 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 '<<================= --- Regards, Norman "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... 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. -- Gary "Norman Jones" wrote in message ... 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 --- Regards, Norman "Patrick Simonds" wrote in message ... 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" wrote in message ... 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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pasting TextBox values
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. --- Regards, Norman "Patrick Simonds" wrote in message ... 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. "Norman Jones" wrote in message ... 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 '<<================= --- Regards, Norman "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... 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. -- Gary "Norman Jones" wrote in message ... 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 --- Regards, Norman "Patrick Simonds" wrote in message ... 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" wrote in message ... 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 '<<================= --- Regards, Norman "Patrick Simonds" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cutting and Pasting Cells into a shape or textbox | Excel Discussion (Misc queries) | |||
| pasting textbox value to active cell | Excel Discussion (Misc queries) | |||
| Pasting to Cells from TextBox User Control | Excel Programming | |||
| How do I add TextBox.values? | Excel Programming | |||
| How do I add TextBox.values? | Excel Discussion (Misc queries) | |||
Linear Mode
