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

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

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

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

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

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