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