Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
text boxes on forms | Excel Programming | |||
User Forms - Combo Boxes | Excel Discussion (Misc queries) | |||
Boxes in Forms | Excel Discussion (Misc queries) | |||
forms / text boxes | Excel Programming | |||
List Boxes New User | New Users to Excel |