MrExcel Publishing
Your One Stop for Excel Tips & Solutions

List Box Macro problem


Posted by Keith on June 22, 2000 2:30 AM

Urgent help required.

I've got a big problem and I can't sort it out.

I have a list box that contains the list of all sheet names in my workbook. When you click on the name it escorts you to that particular sheet.

HOWEVER I have a macro that inserts and deletes sheets then automatically alphabetically sorts the sheets and then an updated list should appear in the list box.

The macro falls over and highlights "SheetName = Range("k44").Text" With an error message "Unable to get the Text property of the range class".

k44 refers to a concatenated cell in j44.

Is there an obvious problem that I can't see?

Please help.

Keith.


My code for the list box is below:

Private Sub ListBox1_Click()
Dim SheetName As String


'In this case assume Sheet called "1 Print"
Application.ScreenUpdating = False
Sheets("1 Print").Select
SheetName = Range("k44").Text
Application.DisplayAlerts = False
On Error Resume Next
Sheets(SheetName).Select
ActiveWindow.DisplayWorkbookTabs = True
End Sub


Posted by Ryan on June 23, 2000 4:05 PM

Keith,

What exactly do you mean by "k44" referring to a concatenated cell IN j44. Explain that one and I could help.

Ryan

Posted by Keith on June 26, 2000 7:25 AM

Ryan,

Yes, it works perfectly. Thanks a lot! You're always a great help!

Posted by Ryan on June 27, 2000 4:46 AM

Email me

Keith, that is fine if you email me. I'll take a look at it.