Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
(I posted this in the office.misc newsgroup by mistake)
I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
ss.Select
but you rarely need to select the sheet, just use the ss variable. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
Try,
Dim ss As String ss = Sheets("Sheet60").Range("d2").Value Sheets(ss).Select Mike "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
hi
you're trying to select too much at once. to select a range, the sheet that has the range must be selected first. dim ws as worksheet dim ss as range set ws = sheets("sheet60") set ss = sheets("sheet60").range("D2") ws.activate ss.select regards FSt1 "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
Mike,
Now says Subscript out of range. Rob "Mike H" wrote in message ... Try, Dim ss As String ss = Sheets("Sheet60").Range("d2").Value Sheets(ss).Select Mike "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
Hi,
Then you don't have a Sheet60 or you don't have a sheet that matches what you have typed in D2 of Sheet60. Check for spelling and spaces. Mike "RobN" wrote: Mike, Now says Subscript out of range. Rob "Mike H" wrote in message ... Try, Dim ss As String ss = Sheets("Sheet60").Range("d2").Value Sheets(ss).Select Mike "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
Bob,
I tried the following without success. Dim ss As Variant Set ss = Sheet60.Range("d2") ss.Select error: Select method of range class failed. Rob "Bob Phillips" wrote in message ... ss.Select but you rarely need to select the sheet, just use the ss variable. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
I think this is getting somewhere, but not quite.
What I have now is: Dim ws As Worksheet Dim ss As Range Set ws = Sheets("SheetNames") Set ss = Sheets("SheetNames").Range("D2") then some other code, and eventually ws.activate ss.select It works to the point of activating the sheet called "SheetNames" by the command ws.activate, but it totally ignores the command ss.select. When I hover my mouse over the ss variable before the procedure ends, it shows "ss = sheet6" which is what I expect, but it doesn't activate that sheet. The formula in the sheets called "SheetNames" in cell D2 is ="Sheet"&VLOOKUP(TRUE,A2:D60,4,FALSE) and the result is Sheet6 If I change that to simply VLOOKUP(TRUE,A2:D60,4,FALSE) the result is 6. But that doesn't work either. Rob "FSt1" wrote in message ... hi you're trying to select too much at once. to select a range, the sheet that has the range must be selected first. dim ws as worksheet dim ss as range set ws = sheets("sheet60") set ss = sheets("sheet60").range("D2") ws.activate ss.select regards FSt1 "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
Fraid I do!
I copied and pasted it here from the VBA projects window --- Sheet60. It's full name is Sheet60 (SheetNames). However, I did try amending your Sheets("Sheet60").Range("d2").Value to Sheets("SheetNames").Range("d2").Value, but that didn't work either. I'm also a bit confused with the command Sheet or Sheets. Which one should it be? Rob "Mike H" wrote in message ... Hi, Then you don't have a Sheet60 or you don't have a sheet that matches what you have typed in D2 of Sheet60. Check for spelling and spaces. Mike "RobN" wrote: Mike, Now says Subscript out of range. Rob "Mike H" wrote in message ... Try, Dim ss As String ss = Sheets("Sheet60").Range("d2").Value Sheets(ss).Select Mike "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
hi
did it select cell D2? that was all it was suppost to do. activating sheet 6 as the result of a formala is another whole code ball game. regards FSt1 "RobN" wrote: I think this is getting somewhere, but not quite. What I have now is: Dim ws As Worksheet Dim ss As Range Set ws = Sheets("SheetNames") Set ss = Sheets("SheetNames").Range("D2") then some other code, and eventually ws.activate ss.select It works to the point of activating the sheet called "SheetNames" by the command ws.activate, but it totally ignores the command ss.select. When I hover my mouse over the ss variable before the procedure ends, it shows "ss = sheet6" which is what I expect, but it doesn't activate that sheet. The formula in the sheets called "SheetNames" in cell D2 is ="Sheet"&VLOOKUP(TRUE,A2:D60,4,FALSE) and the result is Sheet6 If I change that to simply VLOOKUP(TRUE,A2:D60,4,FALSE) the result is 6. But that doesn't work either. Rob "FSt1" wrote in message ... hi you're trying to select too much at once. to select a range, the sheet that has the range must be selected first. dim ws as worksheet dim ss as range set ws = sheets("sheet60") set ss = sheets("sheet60").range("D2") ws.activate ss.select regards FSt1 "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
sorry, I meant
Dim ss As Variant Set ss = Sheet60 ss.Select ss..Range("d2").select -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... Bob, I tried the following without success. Dim ss As Variant Set ss = Sheet60.Range("d2") ss.Select error: Select method of range class failed. Rob "Bob Phillips" wrote in message ... ss.Select but you rarely need to select the sheet, just use the ss variable. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
Hi,
If you confident you have sheets as named then i'm confused but with regard to Sheets("SheetNames").Range("d2").Value That won't work because it will look for a worksheet '"Sheetnames" but this will provided 'Sheet60' is a valid sheet name and the value in D2 is a valid sheet name Sheets(Sheets("Sheet60").Range("d2").Value).Select Mike "RobN" wrote: Fraid I do! I copied and pasted it here from the VBA projects window --- Sheet60. It's full name is Sheet60 (SheetNames). However, I did try amending your Sheets("Sheet60").Range("d2").Value to Sheets("SheetNames").Range("d2").Value, but that didn't work either. I'm also a bit confused with the command Sheet or Sheets. Which one should it be? Rob "Mike H" wrote in message ... Hi, Then you don't have a Sheet60 or you don't have a sheet that matches what you have typed in D2 of Sheet60. Check for spelling and spaces. Mike "RobN" wrote: Mike, Now says Subscript out of range. Rob "Mike H" wrote in message ... Try, Dim ss As String ss = Sheets("Sheet60").Range("d2").Value Sheets(ss).Select Mike "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
Sheets(60).Select
Gord Dibben MS Excel MVP On Thu, 22 May 2008 19:22:07 +0930, "RobN" wrote: Fraid I do! I copied and pasted it here from the VBA projects window --- Sheet60. It's full name is Sheet60 (SheetNames). However, I did try amending your Sheets("Sheet60").Range("d2").Value to Sheets("SheetNames").Range("d2").Value, but that didn't work either. I'm also a bit confused with the command Sheet or Sheets. Which one should it be? Rob "Mike H" wrote in message ... Hi, Then you don't have a Sheet60 or you don't have a sheet that matches what you have typed in D2 of Sheet60. Check for spelling and spaces. Mike "RobN" wrote: Mike, Now says Subscript out of range. Rob "Mike H" wrote in message ... Try, Dim ss As String ss = Sheets("Sheet60").Range("d2").Value Sheets(ss).Select Mike "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
Thanks to all who contributed. I don't think my problem was fully
understood, but I think I've managed to compile a solution from using bits from all the answers. Rob "Bob Phillips" wrote in message ... sorry, I meant Dim ss As Variant Set ss = Sheet60 ss.Select ss..Range("d2").select -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... Bob, I tried the following without success. Dim ss As Variant Set ss = Sheet60.Range("d2") ss.Select error: Select method of range class failed. Rob "Bob Phillips" wrote in message ... ss.Select but you rarely need to select the sheet, just use the ss variable. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "RobN" wrote in message ... (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting sheet with VB
I thought I had this solved, but I find now that the procedure doesn't find
the right sheet. I don't understand because it appears the code and formulas are correct and the sheet numbers match the sheet names, etc. If it's not too much trouble for you or someone else, I've pasted the full code. The code opens a userform from which one of the sheet names is selected. The selection then puts TRUE in the appropriate row of column A in the sheet, "SheetNames". The formula in D2 finds the matching sheet number using the formula =VLOOKUP(TRUE,A3:D56,4,FALSE). All this works fine. What doesn't seem to work is the line... Sheets(ss).Select It does select a sheet, but not the correct sheet as it should, according to the number shown in D2. For instance, if I select, "NSW-MSE-F" in the userform, it will show the number 36 in D2 in the sheet, "SheetNames, but sheet "SA-MSE-F" is selected instead, which is sheet 18. I've changed the order of the sheets to conform with the order they appear in the userform list, but that has now made the situation worse. Does it mean that I need to renumber every sheet so it corresponds with it's tab position for this thing to work? If so, is there a macro that will do that for me quickly? Private Sub OKButton_Click() On Error GoTo Endit Application.Calculation = xlCalculationManual Dim ss As Variant Set ss = Sheets("SheetNames").Range("D2") With Sheet60 ..Range("a3") = ListBox1.Selected(0) ..Range("a4") = ListBox1.Selected(1) ..Range("a5") = ListBox1.Selected(2) ..Range("a6") = ListBox1.Selected(3) ..Range("a7") = ListBox1.Selected(4) ..Range("a8") = ListBox1.Selected(5) ..Range("a9") = ListBox1.Selected(6) ..Range("a10") = ListBox1.Selected(7) ..Range("a11") = ListBox1.Selected(8) ..Range("a12") = ListBox1.Selected(9) ..Range("a13") = ListBox1.Selected(10) ..Range("a14") = ListBox1.Selected(11) ..Range("a15") = ListBox1.Selected(12) ..Range("a16") = ListBox1.Selected(13) ..Range("a17") = ListBox1.Selected(14) ..Range("a18") = ListBox1.Selected(15) ..Range("a19") = ListBox1.Selected(16) ..Range("a20") = ListBox1.Selected(17) ..Range("a21") = ListBox1.Selected(18) ..Range("a22") = ListBox1.Selected(19) ..Range("a23") = ListBox1.Selected(20) ..Range("a24") = ListBox1.Selected(21) ..Range("a25") = ListBox1.Selected(22) ..Range("a26") = ListBox1.Selected(23) ..Range("a27") = ListBox1.Selected(24) ..Range("a28") = ListBox1.Selected(25) ..Range("a29") = ListBox1.Selected(26) ..Range("a30") = ListBox1.Selected(27) ..Range("a31") = ListBox1.Selected(28) ..Range("a32") = ListBox1.Selected(29) ..Range("a33") = ListBox1.Selected(30) ..Range("a34") = ListBox1.Selected(31) ..Range("a35") = ListBox1.Selected(32) ..Range("a36") = ListBox1.Selected(33) ..Range("a37") = ListBox1.Selected(34) ..Range("a38") = ListBox1.Selected(35) ..Range("a39") = ListBox1.Selected(36) ..Range("a40") = ListBox1.Selected(37) ..Range("a41") = ListBox1.Selected(38) ..Range("a42") = ListBox1.Selected(39) ..Range("a43") = ListBox1.Selected(40) ..Range("a44") = ListBox1.Selected(41) ..Range("a45") = ListBox1.Selected(42) ..Range("a46") = ListBox1.Selected(43) ..Range("a47") = ListBox1.Selected(44) ..Range("a48") = ListBox1.Selected(45) ..Range("a49") = ListBox1.Selected(46) ..Range("a50") = ListBox1.Selected(47) ..Range("a51") = ListBox1.Selected(48) ..Range("a52") = ListBox1.Selected(49) ..Range("a53") = ListBox1.Selected(50) ..Range("a54") = ListBox1.Selected(51) ..Range("a55") = ListBox1.Selected(52) ..Range("a56") = ListBox1.Selected(53) End With Unload Me Application.Calculation = xlCalculationAutomatic Sheets(ss).Select Endit: End Sub Private Sub UserForm_Initialize() With ufSelectSheet.ListBox1 .RowSource = "" .AddItem "ACT-MSE-F" .AddItem "ACT-MSE-U" .AddItem "ACT-G-F" .AddItem "ACT-G-UF" .AddItem "ACT-R" .AddItem "ACT-L" .AddItem "ANO-MSE-F" .AddItem "ANO-MSE-U" .AddItem "ANO-G-F" .AddItem "ANO-G-UF" .AddItem "ANO-R" .AddItem "ANO-L" .AddItem "NSW-MSE-F" .AddItem "NSW-MSE-U" .AddItem "NSW-G-F" .AddItem "NSW-G-UF" .AddItem "NSW-R" .AddItem "NSW-L" .AddItem "NT-MSE-F" .AddItem "NT-MSE-U" .AddItem "NT-G-F" .AddItem "NT-G-UF" .AddItem "NT-R" .AddItem "NT-L" .AddItem "QLD-MSE-F" .AddItem "QLD-MSE-U" .AddItem "QLD-G-F" .AddItem "QLD-G-UF" .AddItem "QLD-R" .AddItem "QLD-L" .AddItem "SA-MSE-F" .AddItem "SA-MSE-U" .AddItem "SA-G-F" .AddItem "SA-G-UF" .AddItem "SA-R" .AddItem "SA-L" .AddItem "TAS-MSE-F" .AddItem "TAS-MSE-U" .AddItem "TAS-G-F" .AddItem "TAS-G-UF" .AddItem "TAS-R" .AddItem "TAS-L" .AddItem "VIC-MSE-F" .AddItem "VIC-MSE-U" .AddItem "VIC-G-F" .AddItem "VIC-G-UF" .AddItem "VIC-R" .AddItem "VIC-L" .AddItem "WA-MSE-F" .AddItem "WA-MSE-U" .AddItem "WA-G-F" .AddItem "WA-G-UF" .AddItem "WA-R" .AddItem "WA-L" .MultiSelect = fmMultiSelectSingle End With End Sub Thanks for any help!! Rob "FSt1" wrote in message ... hi did it select cell D2? that was all it was suppost to do. activating sheet 6 as the result of a formala is another whole code ball game. regards FSt1 "RobN" wrote: I think this is getting somewhere, but not quite. What I have now is: Dim ws As Worksheet Dim ss As Range Set ws = Sheets("SheetNames") Set ss = Sheets("SheetNames").Range("D2") then some other code, and eventually ws.activate ss.select It works to the point of activating the sheet called "SheetNames" by the command ws.activate, but it totally ignores the command ss.select. When I hover my mouse over the ss variable before the procedure ends, it shows "ss = sheet6" which is what I expect, but it doesn't activate that sheet. The formula in the sheets called "SheetNames" in cell D2 is ="Sheet"&VLOOKUP(TRUE,A2:D60,4,FALSE) and the result is Sheet6 If I change that to simply VLOOKUP(TRUE,A2:D60,4,FALSE) the result is 6. But that doesn't work either. Rob "FSt1" wrote in message ... hi you're trying to select too much at once. to select a range, the sheet that has the range must be selected first. dim ws as worksheet dim ss as range set ws = sheets("sheet60") set ss = sheets("sheet60").range("D2") ws.activate ss.select regards FSt1 "RobN" wrote: (I posted this in the office.misc newsgroup by mistake) I've used the following to try and have VB select a sheet dependant on the value in d2. Dim ss As Variant Set ss = Sheet60.Range("d2") How do I apply that now to select that sheet? I thought something like sheet(ss).select would do it, but it doesn't work Sheets(ss).select does work, but doesn't go to the correct sheet number, whereas Sheet(ss) brings up a "Sub or Function not defined" error. Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to update a ComboBox when selecting a sheet | Excel Worksheet Functions | |||
Display MsgBox when selecting a sheet | Excel Discussion (Misc queries) | |||
Selecting active area of sheet | Excel Discussion (Misc queries) | |||
Selecting Last Sheet | Excel Worksheet Functions | |||
Selecting a sheet from a drop down box | Excel Discussion (Misc queries) |