Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going to a "set" range
‘User clicks a button in UserForm5 (UF5) to choose a vehicle to edit.
Each vehicle is in it’s own sheet, V#1, V#2, V#3, etc. User is taken to UF9 to make edit changes. When editing is pasted to proper sheet (V#1 in example below), I want to go to the sheet Vehicle Summary, call the range indicated below in the UF5 code, and paste the rest of the changes from the UF9 edit. This is in UF5 Private Sub CommandButton1_Click() 'Edit V #1 ‘Dim and Set here is attempt to put range “V__1” into memory to call later in UF9 Dim rng As Range Set rng = Worksheets("Vehicle Summary").Range("V__1") ‘NOTE: I’ve also tried putting the Dim statement above in the Declarations section. Also didn’t work. ‘This will bring up UF9 so that user can edit info, which is in sheet V#1 Sheets("V #1").Select Application.Goto Range("A1"), True Unload UserForm5 UserForm9.TextBox1.SetFocus UserForm9.Show End Sub ‘In UF9, this command fills textboxes with correct info for user to edit. Private Sub Userform_Activate() 'Populates the fields ShowToEdit End Sub ‘After form is populated to show vehicle chosen before, user makes changes, then clicks this button, which will cause the changes made to the proper vehicle sheet. That code is also in UF9. Private Sub CommandButton5_Click() EditVehicle 'goes to code below to paste changes End Sub ‘At this point everything works fine. The correct changes are made to the proper sheet. All the changes come out correctly. What I want it to do now is to continue by going to sheet Vehicle Summary, then go to the range name indicated above (V#1 in this example). There is more instructions after this that will work if I get to the range. ‘This is at the end of the Pasting routine in UF9. It takes me to the correct sheet, but errors out in the rng.Active line. Sheets("Vehicle summary").Select rng.Activate ‘THIS LINE is highlighted with the error. I’ve also tried different ways of ‘calling the range, with no success. Obviously, I’m not doing the Dim/Set statements correctly. I can’t make the range settings in UF9, because I want this to be generic code for all the vehicles to use rather than to write it out 10 times. I’ve searched all day for examples here, but I don’t see anything that applies to what I’m doing. Any help will be appreciated. Thanks jeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going to a "set" range
you can't activate a range that's not on the active sheet. you probably
don't need to either. to populate a cell with data worksheet("x").Range("A1") = worksheets("b").Range("C2").value or, with your code, something like this rng.value = worksheets("v#2").Range("V__2").Value wrote in message ... ‘User clicks a button in UserForm5 (UF5) to choose a vehicle to edit. Each vehicle is in it’s own sheet, V#1, V#2, V#3, etc. User is taken to UF9 to make edit changes. When editing is pasted to proper sheet (V#1 in example below), I want to go to the sheet Vehicle Summary, call the range indicated below in the UF5 code, and paste the rest of the changes from the UF9 edit. This is in UF5 Private Sub CommandButton1_Click() 'Edit V #1 ‘Dim and Set here is attempt to put range “V__1” into memory to call later in UF9 Dim rng As Range Set rng = Worksheets("Vehicle Summary").Range("V__1") ‘NOTE: I’ve also tried putting the Dim statement above in the Declarations section. Also didn’t work. ‘This will bring up UF9 so that user can edit info, which is in sheet V#1 Sheets("V #1").Select Application.Goto Range("A1"), True Unload UserForm5 UserForm9.TextBox1.SetFocus UserForm9.Show End Sub ‘In UF9, this command fills textboxes with correct info for user to edit. Private Sub Userform_Activate() 'Populates the fields ShowToEdit End Sub ‘After form is populated to show vehicle chosen before, user makes changes, then clicks this button, which will cause the changes made to the proper vehicle sheet. That code is also in UF9. Private Sub CommandButton5_Click() EditVehicle 'goes to code below to paste changes End Sub ‘At this point everything works fine. The correct changes are made to the proper sheet. All the changes come out correctly. What I want it to do now is to continue by going to sheet Vehicle Summary, then go to the range name indicated above (V#1 in this example). There is more instructions after this that will work if I get to the range. ‘This is at the end of the Pasting routine in UF9. It takes me to the correct sheet, but errors out in the rng.Active line. Sheets("Vehicle summary").Select rng.Activate ‘THIS LINE is highlighted with the error. I’ve also tried different ways of ‘calling the range, with no success. Obviously, I’m not doing the Dim/Set statements correctly. I can’t make the range settings in UF9, because I want this to be generic code for all the vehicles to use rather than to write it out 10 times. I’ve searched all day for examples here, but I don’t see anything that applies to what I’m doing. Any help will be appreciated. Thanks jeff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going to a "set" range
On May 14, 10:06*am, "Patrick Molloy"
wrote: you can't activate a range that's not on the active sheet. you probably don't need to either. to populate a cell with data worksheet("x").Range("A1") = worksheets("b").Range("C2").value or, with your code, something like this rng.value = worksheets("v#2").Range("V__2").Value wrote in message ... ‘User clicks a button in UserForm5 (UF5) to choose a vehicle to edit. Each vehicle is in it’s own sheet, V#1, V#2, V#3, etc. User is taken to UF9 to make edit changes. *When editing is pasted to proper sheet (V#1 in example below), * I want to go to the sheet Vehicle Summary, call the range indicated below in the UF5 code, and paste the rest of the changes from the UF9 edit. This is in UF5 Private Sub CommandButton1_Click() 'Edit V #1 ‘Dim and Set here is attempt to put range “V__1” into memory to call later in UF9 Dim rng As Range Set rng = Worksheets("Vehicle Summary").Range("V__1") ‘NOTE: I’ve also tried putting the Dim statement above in the Declarations section. Also didn’t work. ‘This will bring up UF9 so that user can edit info, which is in sheet V#1 Sheets("V #1").Select Application.Goto Range("A1"), True Unload UserForm5 UserForm9.TextBox1.SetFocus UserForm9.Show End Sub ‘In UF9, *this command fills textboxes with correct info for user to edit. Private Sub Userform_Activate() 'Populates the fields *ShowToEdit * End Sub ‘After form is populated *to show vehicle chosen before, user makes changes, then clicks this button, which will cause the changes made to the proper vehicle sheet. That code is also in UF9. Private Sub CommandButton5_Click() EditVehicle *'goes to code below to paste changes End Sub ‘At this point everything works fine. The correct changes are made to the proper sheet. All the changes come out correctly. What I want it to do now is to continue by going to sheet Vehicle Summary, then go to the range name indicated above (V#1 in this example). There is more instructions after this that will work if I get to the range. ‘This is at the end of the Pasting routine in UF9. * *It takes me to the correct sheet, but errors out in the rng.Active line. Sheets("Vehicle summary").Select rng.Activate * ‘THIS LINE is highlighted with the error. I’ve also tried different ways of ‘calling the range, with no success. Obviously, I’m not doing the Dim/Set statements correctly. I can’t make the range settings in UF9, because I want this to be generic code for all the vehicles to use rather than to write it out 10 times. I’ve searched all day for examples here, but I don’t see anything that applies to what I’m doing. Any help will be appreciated. Thanks jeff- Hide quoted text - Ok, your saying I don't use a Dim statement. Let's say I use this code. rng.value = worksheets("v#2").Range("V__2").Value I assumme I put this in UF5 ? Then, in UF9, how do I "goto" this range? something like?? Sheets("Vehicle summary").Select rng.Activate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Going to a "set" range
On May 14, 10:25*am, wrote:
On May 14, 10:06*am, "Patrick Molloy" wrote: you can't activate a range that's not on the active sheet. you probably don't need to either. to populate a cell with data worksheet("x").Range("A1") = worksheets("b").Range("C2").value or, with your code, something like this rng.value = worksheets("v#2").Range("V__2").Value wrote in message ... ‘User clicks a button in UserForm5 (UF5) to choose a vehicle to edit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
"Subscript out of range" error for: Workbooks("Test1.xls").Save | Excel Programming | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming |