User Forms and List Boxes
 

I have a User Form and inside is a ListBox that shows a list of Different
Worksheets within the Workbook. I asked a collegue for help to write the
Code that when you click on the WorkSheet name in the workbook, it would open
that workbook.

I copied his code into the ListBox from his workbook to mine into the Same
ListBox name. It does not work in mine but works fine in his!

I checked to make sure the Properties are similar between the 2 which they
are. What am I missing?
--
MJM

User Forms and List Boxes
 

"MikeM" schreef in bericht
...
I have a User Form and inside is a ListBox that shows a list of Different
Worksheets within the Workbook. I asked a collegue for help to write the
Code that when you click on the WorkSheet name in the workbook, it would
open
that workbook.

I copied his code into the ListBox from his workbook to mine into the Same
ListBox name. It does not work in mine but works fine in his!

I checked to make sure the Properties are similar between the 2 which they
are. What am I missing?
--
MJM


Better paste some code snippets next time, else how can we know what you're
missing?
Two subs below, one fills the listbox, the other one handles the
onclick-event.



'populate listbox with sheets
'when opening the form...
Private Sub UserForm_Activate()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
ListBox1.AddItem ws.Name
Next
Set ws = Nothing
Set wb = Nothing
End Sub

'after clicking the listbox
'activate selected sheet and
'close the form
Private Sub ListBox1_Click()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets(ListBox1.Text)
ws.Activate
Unload Me
Set ws = Nothing
Set wb = Nothing
End Sub

User Forms and List Boxes
 

Here goes, I'm attaching the Code that I have in my Listbox4. I'm was under
the assumption, (I know...), that since I have within the List Box a
RowSource value that refers to a Range that has the Names that populates the
ListBox it would understand my request. When I copy this code into the
ListBox4 on the New Worksheet which is basically a exact copy of the Workbook
that my collegue has the code working, it does not work on my Sheet?
I also copied the Code below which I believe you are talking about that
loads the "List" in ListBox4? This is named: " Sub LoadLB4() "

Thanks for your help!

Private Sub ListBox4_Click()
'***THIS IS THE Segment Selection Screen
'sets the number code corresponding to the selected segment.
'this opens the appropriate 'seg(n)' tab

Dim rng As Range
Set rng = Sheets("1. Segment Prioritization").Range("v1")
Counter = 0
For i = 0 To ListBox4.ListCount - 1
If ListBox4.Selected(i) = True Then
Counter = Counter + 1
rng(Counter).Value = ListBox4.List(i)
End If
Next
Unload Me

Application.ScreenUpdating = False

Sheets("1. Segment Prioritization").Select
Range("w1").Select

If ActiveCell = "1" Then Sheets("Seg (1)").Select Range("E23").Select
If ActiveCell = "2" Then Sheets("Seg (2)").Select Range("E23").Select
If ActiveCell = "3" Then Sheets("Seg (3)").Select Range("E23").Select
If ActiveCell = "4" Then Sheets("Seg (4)").Select Range("E23").Select
If ActiveCell = "5" Then Sheets("Seg (5)").Select Range("E23").Select
If ActiveCell = "6" Then Sheets("Seg (6)").Select Range("E23").Select
If ActiveCell = "7" Then Sheets("Seg (7)").Select Range("E23").Select
If ActiveCell = "8" Then Sheets("Seg (8)").Select Range("E23").Select
If ActiveCell = "9" Then Sheets("Seg (9)").Select Range("E23").Select
If ActiveCell = "10" Then Sheets("Seg (10)").Select Range("E23").Select
If ActiveCell = "11" Then Sheets("Seg (11)").Select Range("E23").Select
If ActiveCell = "12" Then Sheets("Seg (12)").Select Range("E23").Select
If ActiveCell = "13" Then Sheets("Seg (13)").Select Range("E23").Select
If ActiveCell = "14" Then Sheets("Seg (14)").Select Range("E23").Select
If ActiveCell = "15" Then Sheets("Seg (15)").Select Range("E23").Select

End Sub
--

Sub LoadLB4()
'****LOADS Segment names into ListBox 4
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The segments are in A301:A358
Sheets("1. Segment Prioritization").Select
Set AllCells = Range("v11:v25")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0


' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
MENU.ListBox4.AddItem Item
Next Item

'set focus to listbox2
'Menu.ListBox4.SetFocus

End Sub






MJM


"moon" wrote:


"MikeM" schreef in bericht
...
I have a User Form and inside is a ListBox that shows a list of Different
Worksheets within the Workbook. I asked a collegue for help to write the
Code that when you click on the WorkSheet name in the workbook, it would
open
that workbook.

I copied his code into the ListBox from his workbook to mine into the Same
ListBox name. It does not work in mine but works fine in his!

I checked to make sure the Properties are similar between the 2 which they
are. What am I missing?
--
MJM


Better paste some code snippets next time, else how can we know what you're
missing?
Two subs below, one fills the listbox, the other one handles the
onclick-event.



'populate listbox with sheets
'when opening the form...
Private Sub UserForm_Activate()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
ListBox1.AddItem ws.Name
Next
Set ws = Nothing
Set wb = Nothing
End Sub

'after clicking the listbox
'activate selected sheet and
'close the form
Private Sub ListBox1_Click()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets(ListBox1.Text)
ws.Activate
Unload Me
Set ws = Nothing
Set wb = Nothing
End Sub

User Forms and List Boxes
 

Sorry, I'm attaching the 2 codes that I have. I was under the assumption, (
I know...), that since ListBox4 had a RowSource reference in it to a Range,
which popuates the LB with the Names, it would understand what I whish to do?
Quess not. I copied the First Code as written into my WB which is basically
a copy of the WB that my collegue wrote the code and it works. I'm also
sending the code that I believe you are talking about that will load the
names into ListBox4? Its named: " Sub LoadLB4() "

Private Sub ListBox4_Click()
'***THIS IS THE Segment Selection Screen
'sets the number code corresponding to the selected segment.
'this opens the appropriate 'seg(n)' tab

Dim rng As Range
Set rng = Sheets("1. Segment Prioritization").Range("v1")
Counter = 0
For i = 0 To ListBox4.ListCount - 1
If ListBox4.Selected(i) = True Then
Counter = Counter + 1
rng(Counter).Value = ListBox4.List(i)
End If
Next
Unload Me

Application.ScreenUpdating = False

Sheets("1. Segment Prioritization").Select
Range("w1").Select

If ActiveCell = "1" Then Sheets("Seg (1)").Select Range("E23").Select
If ActiveCell = "2" Then Sheets("Seg (2)").Select Range("E23").Select
If ActiveCell = "3" Then Sheets("Seg (3)").Select Range("E23").Select
If ActiveCell = "4" Then Sheets("Seg (4)").Select Range("E23").Select
If ActiveCell = "5" Then Sheets("Seg (5)").Select Range("E23").Select
If ActiveCell = "6" Then Sheets("Seg (6)").Select Range("E23").Select
If ActiveCell = "7" Then Sheets("Seg (7)").Select Range("E23").Select
If ActiveCell = "8" Then Sheets("Seg (8)").Select Range("E23").Select
If ActiveCell = "9" Then Sheets("Seg (9)").Select Range("E23").Select
If ActiveCell = "10" Then Sheets("Seg (10)").Select Range("E23").Select
If ActiveCell = "11" Then Sheets("Seg (11)").Select Range("E23").Select
If ActiveCell = "12" Then Sheets("Seg (12)").Select Range("E23").Select
If ActiveCell = "13" Then Sheets("Seg (13)").Select Range("E23").Select
If ActiveCell = "14" Then Sheets("Seg (14)").Select Range("E23").Select
If ActiveCell = "15" Then Sheets("Seg (15)").Select Range("E23").Select

End Sub

Sub LoadLB4()
'****LOADS Segment names into ListBox 4
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The segments are in A301:A358
Sheets("1. Segment Prioritization").Select
Set AllCells = Range("v11:v25")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0


' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
MENU.ListBox4.AddItem Item
Next Item

'set focus to listbox2
'Menu.ListBox4.SetFocus

End Sub


Thanks for your time and help!

"moon" wrote:


"MikeM" schreef in bericht
...
I have a User Form and inside is a ListBox that shows a list of Different
Worksheets within the Workbook. I asked a collegue for help to write the
Code that when you click on the WorkSheet name in the workbook, it would
open
that workbook.

I copied his code into the ListBox from his workbook to mine into the Same
ListBox name. It does not work in mine but works fine in his!

I checked to make sure the Properties are similar between the 2 which they
are. What am I missing?
--
MJM


Better paste some code snippets next time, else how can we know what you're
missing?
Two subs below, one fills the listbox, the other one handles the
onclick-event.



'populate listbox with sheets
'when opening the form...
Private Sub UserForm_Activate()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
ListBox1.AddItem ws.Name
Next
Set ws = Nothing
Set wb = Nothing
End Sub

'after clicking the listbox
'activate selected sheet and
'close the form
Private Sub ListBox1_Click()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets(ListBox1.Text)
ws.Activate
Unload Me
Set ws = Nothing
Set wb = Nothing
End Sub

User Forms and List Boxes
 

Sorry, I'm attaching the 2 codes that I have. I was under the assumption, (
I know...), that since ListBox4 had a RowSource reference in it to a Range,
which popuates the LB with the Names, it would understand what I whish to do?
Quess not. I copied the First Code as written into my WB which is basically
a copy of the WB that my collegue wrote the code and it works. I'm also
sending the code that I believe you are talking about that will load the
names into ListBox4? Its named: " Sub LoadLB4() "

Private Sub ListBox4_Click()
'***THIS IS THE Segment Selection Screen
'sets the number code corresponding to the selected segment.
'this opens the appropriate 'seg(n)' tab

Dim rng As Range
Set rng = Sheets("1. Segment Prioritization").Range("v1")
Counter = 0
For i = 0 To ListBox4.ListCount - 1
If ListBox4.Selected(i) = True Then
Counter = Counter + 1
rng(Counter).Value = ListBox4.List(i)
End If
Next
Unload Me

Sheets("1. Segment Prioritization").Select
Range("w1").Select

If ActiveCell = "1" Then Sheets("Seg (1)").Select Range("E23").Select
If ActiveCell = "2" Then Sheets("Seg (2)").Select Range("E23").Select
If ActiveCell = "3" Then Sheets("Seg (3)").Select Range("E23").Select
If ActiveCell = "4" Then Sheets("Seg (4)").Select Range("E23").Select
If ActiveCell = "5" Then Sheets("Seg (5)").Select Range("E23").Select
If ActiveCell = "6" Then Sheets("Seg (6)").Select Range("E23").Select

End Sub

Sub LoadLB4()
'****LOADS Segment names into ListBox 4
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The segments are in A301:A358
Sheets("1. Segment Prioritization").Select
Set AllCells = Range("v11:v25")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
MENU.ListBox4.AddItem Item
Next Item

'set focus to listbox2
'Menu.ListBox4.SetFocus


--
MJM


"moon" wrote:


"MikeM" schreef in bericht
...
I have a User Form and inside is a ListBox that shows a list of Different
Worksheets within the Workbook. I asked a collegue for help to write the
Code that when you click on the WorkSheet name in the workbook, it would
open
that workbook.

I copied his code into the ListBox from his workbook to mine into the Same
ListBox name. It does not work in mine but works fine in his!

I checked to make sure the Properties are similar between the 2 which they
are. What am I missing?
--
MJM


Better paste some code snippets next time, else how can we know what you're
missing?
Two subs below, one fills the listbox, the other one handles the
onclick-event.



'populate listbox with sheets
'when opening the form...
Private Sub UserForm_Activate()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
ListBox1.AddItem ws.Name
Next
Set ws = Nothing
Set wb = Nothing
End Sub

'after clicking the listbox
'activate selected sheet and
'close the form
Private Sub ListBox1_Click()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets(ListBox1.Text)
ws.Activate
Unload Me
Set ws = Nothing
Set wb = Nothing
End Sub

User Forms and List Boxes
 

If you have a rowsource set, you shouldn't use additem. If you want to use
AddItem, then don't have a rowsource.

The code you show for
LoadLB4

is a site localization for code from John Walkenbach's site and I have used
it/recommended it many times. So I know it works

However, if the listbox has a rowsource property with an assignment, then I
get a permission denied error.

--
Regards,
Tom Ogilvy

"MikeM" wrote in message
...
Sorry, I'm attaching the 2 codes that I have. I was under the assumption,
(
I know...), that since ListBox4 had a RowSource reference in it to a
Range,
which popuates the LB with the Names, it would understand what I whish to
do?
Quess not. I copied the First Code as written into my WB which is
basically
a copy of the WB that my collegue wrote the code and it works. I'm also
sending the code that I believe you are talking about that will load the
names into ListBox4? Its named: " Sub LoadLB4() "

Private Sub ListBox4_Click()
'***THIS IS THE Segment Selection Screen
'sets the number code corresponding to the selected segment.
'this opens the appropriate 'seg(n)' tab

Dim rng As Range
Set rng = Sheets("1. Segment Prioritization").Range("v1")
Counter = 0
For i = 0 To ListBox4.ListCount - 1
If ListBox4.Selected(i) = True Then
Counter = Counter + 1
rng(Counter).Value = ListBox4.List(i)
End If
Next
Unload Me

Sheets("1. Segment Prioritization").Select
Range("w1").Select

If ActiveCell = "1" Then Sheets("Seg (1)").Select Range("E23").Select
If ActiveCell = "2" Then Sheets("Seg (2)").Select Range("E23").Select
If ActiveCell = "3" Then Sheets("Seg (3)").Select Range("E23").Select
If ActiveCell = "4" Then Sheets("Seg (4)").Select Range("E23").Select
If ActiveCell = "5" Then Sheets("Seg (5)").Select Range("E23").Select
If ActiveCell = "6" Then Sheets("Seg (6)").Select Range("E23").Select

End Sub

Sub LoadLB4()
'****LOADS Segment names into ListBox 4
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The segments are in A301:A358
Sheets("1. Segment Prioritization").Select
Set AllCells = Range("v11:v25")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
MENU.ListBox4.AddItem Item
Next Item

'set focus to listbox2
'Menu.ListBox4.SetFocus


--
MJM


"moon" wrote:


"MikeM" schreef in bericht
...
I have a User Form and inside is a ListBox that shows a list of
Different
Worksheets within the Workbook. I asked a collegue for help to write
the
Code that when you click on the WorkSheet name in the workbook, it
would
open
that workbook.

I copied his code into the ListBox from his workbook to mine into the
Same
ListBox name. It does not work in mine but works fine in his!

I checked to make sure the Properties are similar between the 2 which
they
are. What am I missing?
--
MJM


Better paste some code snippets next time, else how can we know what
you're
missing?
Two subs below, one fills the listbox, the other one handles the
onclick-event.



'populate listbox with sheets
'when opening the form...
Private Sub UserForm_Activate()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
ListBox1.AddItem ws.Name
Next
Set ws = Nothing
Set wb = Nothing
End Sub

'after clicking the listbox
'activate selected sheet and
'close the form
Private Sub ListBox1_Click()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets(ListBox1.Text)
ws.Activate
Unload Me
Set ws = Nothing
Set wb = Nothing
End Sub