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

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

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

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