Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
? To Tom Ogilvy or others on VBA Listbox
Tom,
A week or two back you responded to a question on how to program a Listbox into the system. I have tried everything to make this work but have consistently gotten the message "The macro "...xls'!Box_Click' cannot be found." What am I doing wrong? The code you wrote is: ================================================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Me.ListBoxes.Delete On Error GoTo 0 If Target.Column = 1 Then With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _ ActiveCell.Top, 100, 150) .Name = "Listbox1" .ControlFormat.ListFillRange = "Sheet2!a1:a18" End With Me.ListBoxes("Listbox1").OnAction = "Box_Click" End If End Sub In a general module (no the module associated with the sheet) put in this code Sub Box_Click() Set lbox = ActiveSheet.ListBoxes(Application.Caller) sVal = lbox.List(lbox.ListIndex) ActiveCell.Value = sVal End Sub ================================================== = Thanks for any help you can give. Brad K. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
? To Tom Ogilvy or others on VBA Listbox
I tried your code, with the following objects in Project Explorer under VBE:
-Sheet1 -Sheet2 -Sheet3 -Module1 Placed the Private Sub WorkSheet_SelectionChange......End Sub under sheet1, and Sub Box_Click() ....End Sub under Module1, no problem. The only thing that I have to change is the Worksheet(3) to Sheet1 for the quick test that I did. Since, from memory, the Worksheets(3) depends on how the worksheet got indexed. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
? To Tom Ogilvy or others on VBA Listbox
Whoops, should have mentioned Excel 2002 and Windows 2000.
Brad "Brad K." wrote: Tom, A week or two back you responded to a question on how to program a Listbox into the system. I have tried everything to make this work but have consistently gotten the message "The macro "...xls'!Box_Click' cannot be found." What am I doing wrong? The code you wrote is: ================================================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Me.ListBoxes.Delete On Error GoTo 0 If Target.Column = 1 Then With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _ ActiveCell.Top, 100, 150) .Name = "Listbox1" .ControlFormat.ListFillRange = "Sheet2!a1:a18" End With Me.ListBoxes("Listbox1").OnAction = "Box_Click" End If End Sub In a general module (no the module associated with the sheet) put in this code Sub Box_Click() Set lbox = ActiveSheet.ListBoxes(Application.Caller) sVal = lbox.List(lbox.ListIndex) ActiveCell.Value = sVal End Sub ================================================== = Thanks for any help you can give. Brad K. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
? To Tom Ogilvy or others on VBA Listbox
Works for me. Did you put the Box_Click macro in a general module, not the
sheet module? -- HTH RP (remove nothere from the email address if mailing direct) "Brad K." wrote in message ... Tom, A week or two back you responded to a question on how to program a Listbox into the system. I have tried everything to make this work but have consistently gotten the message "The macro "...xls'!Box_Click' cannot be found." What am I doing wrong? The code you wrote is: ================================================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Me.ListBoxes.Delete On Error GoTo 0 If Target.Column = 1 Then With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _ ActiveCell.Top, 100, 150) .Name = "Listbox1" .ControlFormat.ListFillRange = "Sheet2!a1:a18" End With Me.ListBoxes("Listbox1").OnAction = "Box_Click" End If End Sub In a general module (no the module associated with the sheet) put in this code Sub Box_Click() Set lbox = ActiveSheet.ListBoxes(Application.Caller) sVal = lbox.List(lbox.ListIndex) ActiveCell.Value = sVal End Sub ================================================== = Thanks for any help you can give. Brad K. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
? To Tom Ogilvy or others on VBA Listbox
I tested it before posting and it worked for me. Others seem to have had
good luck. If you want to contact me via email and give me your email address, I can send you a sample workbook. -- Regards, Tom Ogilvy "Brad K." wrote in message ... Tom, A week or two back you responded to a question on how to program a Listbox into the system. I have tried everything to make this work but have consistently gotten the message "The macro "...xls'!Box_Click' cannot be found." What am I doing wrong? The code you wrote is: ================================================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Me.ListBoxes.Delete On Error GoTo 0 If Target.Column = 1 Then With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _ ActiveCell.Top, 100, 150) .Name = "Listbox1" .ControlFormat.ListFillRange = "Sheet2!a1:a18" End With Me.ListBoxes("Listbox1").OnAction = "Box_Click" End If End Sub In a general module (no the module associated with the sheet) put in this code Sub Box_Click() Set lbox = ActiveSheet.ListBoxes(Application.Caller) sVal = lbox.List(lbox.ListIndex) ActiveCell.Value = sVal End Sub ================================================== = Thanks for any help you can give. Brad K. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
? To Tom Ogilvy or others on VBA Listbox
Thanks all. I realized that despite what I thought, I was not correctly
putting the Click_Box macro into a general module. One more question, how do I close or delete the Listbox once I have made my selection (other than clicking on a new cell). Thanks, Brad "Tom Ogilvy" wrote: I tested it before posting and it worked for me. Others seem to have had good luck. If you want to contact me via email and give me your email address, I can send you a sample workbook. -- Regards, Tom Ogilvy "Brad K." wrote in message ... Tom, A week or two back you responded to a question on how to program a Listbox into the system. I have tried everything to make this work but have consistently gotten the message "The macro "...xls'!Box_Click' cannot be found." What am I doing wrong? The code you wrote is: ================================================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Me.ListBoxes.Delete On Error GoTo 0 If Target.Column = 1 Then With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _ ActiveCell.Top, 100, 150) .Name = "Listbox1" .ControlFormat.ListFillRange = "Sheet2!a1:a18" End With Me.ListBoxes("Listbox1").OnAction = "Box_Click" End If End Sub In a general module (no the module associated with the sheet) put in this code Sub Box_Click() Set lbox = ActiveSheet.ListBoxes(Application.Caller) sVal = lbox.List(lbox.ListIndex) ActiveCell.Value = sVal End Sub ================================================== = Thanks for any help you can give. Brad K. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
? To Tom Ogilvy or others on VBA Listbox
Brad,
Just put this code in a separate macro. On Error Resume Next Me.ListBoxes.Delete On Error GoTo 0 -- HTH RP (remove nothere from the email address if mailing direct) "Brad K." wrote in message ... Thanks all. I realized that despite what I thought, I was not correctly putting the Click_Box macro into a general module. One more question, how do I close or delete the Listbox once I have made my selection (other than clicking on a new cell). Thanks, Brad "Tom Ogilvy" wrote: I tested it before posting and it worked for me. Others seem to have had good luck. If you want to contact me via email and give me your email address, I can send you a sample workbook. -- Regards, Tom Ogilvy "Brad K." wrote in message ... Tom, A week or two back you responded to a question on how to program a Listbox into the system. I have tried everything to make this work but have consistently gotten the message "The macro "...xls'!Box_Click' cannot be found." What am I doing wrong? The code you wrote is: ================================================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Me.ListBoxes.Delete On Error GoTo 0 If Target.Column = 1 Then With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _ ActiveCell.Top, 100, 150) .Name = "Listbox1" .ControlFormat.ListFillRange = "Sheet2!a1:a18" End With Me.ListBoxes("Listbox1").OnAction = "Box_Click" End If End Sub In a general module (no the module associated with the sheet) put in this code Sub Box_Click() Set lbox = ActiveSheet.ListBoxes(Application.Caller) sVal = lbox.List(lbox.ListIndex) ActiveCell.Value = sVal End Sub ================================================== = Thanks for any help you can give. Brad K. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
? To Tom Ogilvy or others on VBA Listbox
Sub Box_Click()
Dim sVal as String Dim lbox as ListBox Set lbox = ActiveSheet.ListBoxes(Application.Caller) sVal = lbox.List(lbox.ListIndex) ActiveCell.Value = sVal lbox.Delete End Sub -- Regards, Tom Ogilvy "Brad K." wrote in message ... Thanks all. I realized that despite what I thought, I was not correctly putting the Click_Box macro into a general module. One more question, how do I close or delete the Listbox once I have made my selection (other than clicking on a new cell). Thanks, Brad "Tom Ogilvy" wrote: I tested it before posting and it worked for me. Others seem to have had good luck. If you want to contact me via email and give me your email address, I can send you a sample workbook. -- Regards, Tom Ogilvy "Brad K." wrote in message ... Tom, A week or two back you responded to a question on how to program a Listbox into the system. I have tried everything to make this work but have consistently gotten the message "The macro "...xls'!Box_Click' cannot be found." What am I doing wrong? The code you wrote is: ================================================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next Me.ListBoxes.Delete On Error GoTo 0 If Target.Column = 1 Then With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _ ActiveCell.Top, 100, 150) .Name = "Listbox1" .ControlFormat.ListFillRange = "Sheet2!a1:a18" End With Me.ListBoxes("Listbox1").OnAction = "Box_Click" End If End Sub In a general module (no the module associated with the sheet) put in this code Sub Box_Click() Set lbox = ActiveSheet.ListBoxes(Application.Caller) sVal = lbox.List(lbox.ListIndex) ActiveCell.Value = sVal End Sub ================================================== = Thanks for any help you can give. Brad K. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tom Ogilvy | Excel Programming | |||
Tom Ogilvy | Excel Programming | |||
Tom Ogilvy | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |