Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Values in a MultiColumn Listbox
Dim sh as Worksheet
Dim i as Long Dim sName as String varr = Userform1.ListBox1.List for i = lbound(varr,1) to Ubound(varr,1) On Error Resume Next set sh = Worksheets(varr(i,lbound(varr,2)) On Error goto 0 if not sh is nothing then sName = Trim(varr(i,lbound(varr,2)+1)) sh.Name = sName end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... I have a multicolumn listbox (2 cols). Col1 has values A, B C etc that represent actual sheetnames in the activeworkbook. Against each of these values, the user has typed into a textbox on the form, their true sheetnames. So the listbox might look like A Test1 B Test5 etc. How may I now rename the sheets with the user's true sheetnames, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Values in a MultiColumn Listbox
I've modified it slightly to suit my code:
With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(varr(i, LBound(varr, 2))) On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With There are 14 sheetnames in the listbox. User's chosen true names are 1-14 inclusive. The code runs, but for every sheet it skips the lines: sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName so sheets are not renamed. It seems the line Set ws = Worksheets(varr(i, LBound(varr, 2))) is always returning Nothing. Hence the skip. Have I misled you? Here's the first element in varr: The first value in varr looks like this: varr(0) varr(0, 0) "A" ' value in col1 of listbox varr(0, 1) "1" ' value in col2 Could you help further, please? Regards and thanks. "Tom Ogilvy" wrote in message ... Dim sh as Worksheet Dim i as Long Dim sName as String varr = Userform1.ListBox1.List for i = lbound(varr,1) to Ubound(varr,1) On Error Resume Next set sh = Worksheets(varr(i,lbound(varr,2)) On Error goto 0 if not sh is nothing then sName = Trim(varr(i,lbound(varr,2)+1)) sh.Name = sName end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... I have a multicolumn listbox (2 cols). Col1 has values A, B C etc that represent actual sheetnames in the activeworkbook. Against each of these values, the user has typed into a textbox on the form, their true sheetnames. So the listbox might look like A Test1 B Test5 etc. How may I now rename the sheets with the user's true sheetnames, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Values in a MultiColumn Listbox
Base on what you say
set sh = Worksheets(varr(i,lbound(varr,2)) should resolve to set sh = Worksheets("A") I suspect varr(0,0) probably has spaces in it. Try trimming the "A" With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(Trim(varr(i, LBound(varr, 2)))) '<=== On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With "Stuart" wrote in message ... I've modified it slightly to suit my code: With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(varr(i, LBound(varr, 2))) On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With There are 14 sheetnames in the listbox. User's chosen true names are 1-14 inclusive. The code runs, but for every sheet it skips the lines: sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName so sheets are not renamed. It seems the line Set ws = Worksheets(varr(i, LBound(varr, 2))) is always returning Nothing. Hence the skip. Have I misled you? Here's the first element in varr: The first value in varr looks like this: varr(0) varr(0, 0) "A" ' value in col1 of listbox varr(0, 1) "1" ' value in col2 Could you help further, please? Regards and thanks. "Tom Ogilvy" wrote in message ... Dim sh as Worksheet Dim i as Long Dim sName as String varr = Userform1.ListBox1.List for i = lbound(varr,1) to Ubound(varr,1) On Error Resume Next set sh = Worksheets(varr(i,lbound(varr,2)) On Error goto 0 if not sh is nothing then sName = Trim(varr(i,lbound(varr,2)+1)) sh.Name = sName end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... I have a multicolumn listbox (2 cols). Col1 has values A, B C etc that represent actual sheetnames in the activeworkbook. Against each of these values, the user has typed into a textbox on the form, their true sheetnames. So the listbox might look like A Test1 B Test5 etc. How may I now rename the sheets with the user's true sheetnames, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Values in a MultiColumn Listbox
With Workbooks("Sorted " & wkbkname & ".xls")
varr = frmName_Contractors.lbDataCode.List Debug.Print varr(0, 0) gives A in the Immediate window, and looking closely at the values in varr, suggests no spaces Any help? Regards. "Tom Ogilvy" wrote in message ... Base on what you say set sh = Worksheets(varr(i,lbound(varr,2)) should resolve to set sh = Worksheets("A") I suspect varr(0,0) probably has spaces in it. Try trimming the "A" With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(Trim(varr(i, LBound(varr, 2)))) '<=== On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With "Stuart" wrote in message ... I've modified it slightly to suit my code: With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(varr(i, LBound(varr, 2))) On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With There are 14 sheetnames in the listbox. User's chosen true names are 1-14 inclusive. The code runs, but for every sheet it skips the lines: sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName so sheets are not renamed. It seems the line Set ws = Worksheets(varr(i, LBound(varr, 2))) is always returning Nothing. Hence the skip. Have I misled you? Here's the first element in varr: The first value in varr looks like this: varr(0) varr(0, 0) "A" ' value in col1 of listbox varr(0, 1) "1" ' value in col2 Could you help further, please? Regards and thanks. "Tom Ogilvy" wrote in message ... Dim sh as Worksheet Dim i as Long Dim sName as String varr = Userform1.ListBox1.List for i = lbound(varr,1) to Ubound(varr,1) On Error Resume Next set sh = Worksheets(varr(i,lbound(varr,2)) On Error goto 0 if not sh is nothing then sName = Trim(varr(i,lbound(varr,2)+1)) sh.Name = sName end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... I have a multicolumn listbox (2 cols). Col1 has values A, B C etc that represent actual sheetnames in the activeworkbook. Against each of these values, the user has typed into a textbox on the form, their true sheetnames. So the listbox might look like A Test1 B Test5 etc. How may I now rename the sheets with the user's true sheetnames, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Values in a MultiColumn Listbox
' shouldn't need the next statement if you put in the full stop (period)
Workbooks("Sorted " & wkbkname).Activate With Workbooks("Sorted " & wkbkname) varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = .Worksheets(varr(Ndx, LBound(varr, 2))) '<== need full stop here On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(Ndx, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With -- Regards, Tom Ogilvy "Stuart" wrote in message ... 2 of my errors: Firstly the workbook needed to be active, and 2nd, I had changed 'i' to 'Ndx' (because 'i' is already in use) but missed a couple of 'i's in your code(g). Workbooks("Sorted " & wkbkname).Activate With Workbooks("Sorted " & wkbkname) varr = frmName_Contractors.lbDataCode.List 'check for spaces in the value: 'Debug.Print "--" & varr(0, 0) & "<--" 'check the wkbk name is correct 'Debug.Print Workbooks("Sorted " & wkbkname).Name For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(varr(Ndx, LBound(varr, 2))) On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(Ndx, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With Seems to work now. Many thanks for all the help. Regards. "Tom Ogilvy" wrote in message ... Either you have a sheet named "A" or you don't Either varr(0,0) contains "A" with no spaces or it doesn't The purpose of the statement is to trap the mismatch between the left column name and a non-existent sheet. Another possible source of problem is you have used With Workbooks("Sorted " & wkbkname & ".xls") but the worksheet below does not have a period in its name, so if this workbook is not the activeworkbook, the worksheet name is referencing the activeworkbook which may or may not have a sheet with that name, but regardless, is not the intended sheet. On another note, when I check if a value contains spaces or not, I use debug.print "--" & varr(0,0) & "<--" that gives a much better indication. Just a thought. -- Regards, Tom Ogilvy "Stuart" wrote in message ... With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List Debug.Print varr(0, 0) gives A in the Immediate window, and looking closely at the values in varr, suggests no spaces Any help? Regards. "Tom Ogilvy" wrote in message ... Base on what you say set sh = Worksheets(varr(i,lbound(varr,2)) should resolve to set sh = Worksheets("A") I suspect varr(0,0) probably has spaces in it. Try trimming the "A" With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(Trim(varr(i, LBound(varr, 2)))) '<=== On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With "Stuart" wrote in message ... I've modified it slightly to suit my code: With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(varr(i, LBound(varr, 2))) On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With There are 14 sheetnames in the listbox. User's chosen true names are 1-14 inclusive. The code runs, but for every sheet it skips the lines: sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName so sheets are not renamed. It seems the line Set ws = Worksheets(varr(i, LBound(varr, 2))) is always returning Nothing. Hence the skip. Have I misled you? Here's the first element in varr: The first value in varr looks like this: varr(0) varr(0, 0) "A" ' value in col1 of listbox varr(0, 1) "1" ' value in col2 Could you help further, please? Regards and thanks. "Tom Ogilvy" wrote in message ... Dim sh as Worksheet Dim i as Long Dim sName as String varr = Userform1.ListBox1.List for i = lbound(varr,1) to Ubound(varr,1) On Error Resume Next set sh = Worksheets(varr(i,lbound(varr,2)) On Error goto 0 if not sh is nothing then sName = Trim(varr(i,lbound(varr,2)+1)) sh.Name = sName end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... I have a multicolumn listbox (2 cols). Col1 has values A, B C etc that represent actual sheetnames in the activeworkbook. Against each of these values, the user has typed into a textbox on the form, their true sheetnames. So the listbox might look like A Test1 B Test5 etc. How may I now rename the sheets with the user's true sheetnames, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Values in a MultiColumn Listbox
Because I'm in a With Workbook construct and referencing
a Worksheet in the collection....I think. Regards and thanks. "Tom Ogilvy" wrote in message ... ' shouldn't need the next statement if you put in the full stop (period) Workbooks("Sorted " & wkbkname).Activate With Workbooks("Sorted " & wkbkname) varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = .Worksheets(varr(Ndx, LBound(varr, 2))) '<== need full stop here On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(Ndx, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With -- Regards, Tom Ogilvy "Stuart" wrote in message ... 2 of my errors: Firstly the workbook needed to be active, and 2nd, I had changed 'i' to 'Ndx' (because 'i' is already in use) but missed a couple of 'i's in your code(g). Workbooks("Sorted " & wkbkname).Activate With Workbooks("Sorted " & wkbkname) varr = frmName_Contractors.lbDataCode.List 'check for spaces in the value: 'Debug.Print "--" & varr(0, 0) & "<--" 'check the wkbk name is correct 'Debug.Print Workbooks("Sorted " & wkbkname).Name For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(varr(Ndx, LBound(varr, 2))) On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(Ndx, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With Seems to work now. Many thanks for all the help. Regards. "Tom Ogilvy" wrote in message ... Either you have a sheet named "A" or you don't Either varr(0,0) contains "A" with no spaces or it doesn't The purpose of the statement is to trap the mismatch between the left column name and a non-existent sheet. Another possible source of problem is you have used With Workbooks("Sorted " & wkbkname & ".xls") but the worksheet below does not have a period in its name, so if this workbook is not the activeworkbook, the worksheet name is referencing the activeworkbook which may or may not have a sheet with that name, but regardless, is not the intended sheet. On another note, when I check if a value contains spaces or not, I use debug.print "--" & varr(0,0) & "<--" that gives a much better indication. Just a thought. -- Regards, Tom Ogilvy "Stuart" wrote in message ... With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List Debug.Print varr(0, 0) gives A in the Immediate window, and looking closely at the values in varr, suggests no spaces Any help? Regards. "Tom Ogilvy" wrote in message ... Base on what you say set sh = Worksheets(varr(i,lbound(varr,2)) should resolve to set sh = Worksheets("A") I suspect varr(0,0) probably has spaces in it. Try trimming the "A" With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(Trim(varr(i, LBound(varr, 2)))) '<=== On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With "Stuart" wrote in message ... I've modified it slightly to suit my code: With Workbooks("Sorted " & wkbkname & ".xls") varr = frmName_Contractors.lbDataCode.List For Ndx = LBound(varr, 1) To UBound(varr, 1) On Error Resume Next Set ws = Worksheets(varr(i, LBound(varr, 2))) On Error GoTo 0 If Not ws Is Nothing Then sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName End If Next End With There are 14 sheetnames in the listbox. User's chosen true names are 1-14 inclusive. The code runs, but for every sheet it skips the lines: sName = Trim(varr(i, LBound(varr, 2) + 1)) ws.Name = sName so sheets are not renamed. It seems the line Set ws = Worksheets(varr(i, LBound(varr, 2))) is always returning Nothing. Hence the skip. Have I misled you? Here's the first element in varr: The first value in varr looks like this: varr(0) varr(0, 0) "A" ' value in col1 of listbox varr(0, 1) "1" ' value in col2 Could you help further, please? Regards and thanks. "Tom Ogilvy" wrote in message ... Dim sh as Worksheet Dim i as Long Dim sName as String varr = Userform1.ListBox1.List for i = lbound(varr,1) to Ubound(varr,1) On Error Resume Next set sh = Worksheets(varr(i,lbound(varr,2)) On Error goto 0 if not sh is nothing then sName = Trim(varr(i,lbound(varr,2)+1)) sh.Name = sName end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... I have a multicolumn listbox (2 cols). Col1 has values A, B C etc that represent actual sheetnames in the activeworkbook. Against each of these values, the user has typed into a textbox on the form, their true sheetnames. So the listbox might look like A Test1 B Test5 etc. How may I now rename the sheets with the user's true sheetnames, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.515 / Virus Database: 313 - Release Date: 01/09/2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multicolumn adress file. want to sort on last , first name | New Users to Excel | |||
Listbox Values | Excel Discussion (Misc queries) | |||
Transferring items between multicolumn listboxes | Excel Discussion (Misc queries) | |||
ListBox with Horizontal Values | Excel Worksheet Functions | |||
Sorting ListBox results or transposing ListBox values to other cells for sorting | Excel Programming |