Over the last couple of days I modified an existing workbook by adding an ActiveX listbox control which was working as expected (that is, the LinkedCell automatically updated to the selected value in the list).
Today when I open the workbook, everything appears to be the same; but the LinkedCell is not updated.
When I set a VBA breakpoint I discover that me.ListBox1.LinkedCell holds the value ""; and I am unable to set the value.
I'm at a loss to explain what has changed.
Any suggestions?
The listbox was created using the tools on the Developer tab of the ribbon, and is manipulated on the fly by this code:
The full procedure:
Today when I open the workbook, everything appears to be the same; but the LinkedCell is not updated.
When I set a VBA breakpoint I discover that me.ListBox1.LinkedCell holds the value ""; and I am unable to set the value.
I'm at a loss to explain what has changed.
Any suggestions?
The listbox was created using the tools on the Developer tab of the ribbon, and is manipulated on the fly by this code:
VBA Code:
With Me.ListBox1
bVisible = True
Select Case False
Case Application.Intersect(Target, Range(Cem)) Is Nothing
.ListFillRange = Cement
Case Application.Intersect(Target, Range(FA)) Is Nothing
.ListFillRange = FlyAsh
Case Application.Intersect(Target, Range(Rk)) Is Nothing
.ListFillRange = Rock
Case Application.Intersect(Target, Range(Snd)) Is Nothing
.ListFillRange = Sand
Case Else
bVisible = False
End Select
.MultiSelect = fmMultiSelectSingle
.LinkedCell = Target.Address
TargetAddress = Target.Address
.Height = 104.25
.Width = 276.75
.Locked = True
.Placement = xlFreeFloating
.Enabled = True
' .Top = Target.Offset(-9).Top
.Top = Range(Cem).Offset(-9).Top 'vertical same for every column
.Left = Target.Offset(, -1).Left 'but horizontal relative to target
.Visible = bVisible
Stop
End With
VBA Code:
Private Static Sub Worksheet_SelectionChange(ByVal Target As Range)
'---------------------------------------------------------------------------------------
' Procedure : Worksheet_SelectionChange
' Author : cliff
' Date : 9/18/2012
' Purpose : Call/Dismiss Listbox
'---------------------------------------------------------------------------------------
' 12/30/21
' Material Cost tables
Const Admix As String = "Admix"
Const Cement As String = "Cement"
Const FlyAsh As String = "FlyAsh"
Const Misc As String = "Misc"
Const Rock As String = "Rock"
Const Sand As String = "Sand"
Const Water As String = "Water"
' Batch Plant Cost Lines
Const Cem As String = "Cem"
Const FA As String = "FA"
Const Rk As String = "Rk"
Const Snd As String = "Snd"
Const Wat As String = "Wat"
Dim r1 As Range
Dim sumOffset As Long 'Group Summary Row Offset
Dim bVisible As Boolean
'Select Case True
'Case Left([l1].Text, 4) = "***+" '20 cyl format
Set r1 = Application.Intersect(Target, Union( _
Range(Cem), _
Range(FA), _
Range(Rk), _
Range(Snd), _
Range(Wat)))
If r1 Is Nothing Then
Me.ListBox1.Visible = False
Else
With Me.ListBox1
bVisible = True
Select Case False
Case Application.Intersect(Target, Range(Cem)) Is Nothing
.ListFillRange = Cement
Case Application.Intersect(Target, Range(FA)) Is Nothing
.ListFillRange = FlyAsh
Case Application.Intersect(Target, Range(Rk)) Is Nothing
.ListFillRange = Rock
Case Application.Intersect(Target, Range(Snd)) Is Nothing
.ListFillRange = Sand
Case Else
bVisible = False
End Select
.MultiSelect = fmMultiSelectSingle
.LinkedCell = Target.Address
TargetAddress = Target.Address
.Height = 104.25
.Width = 276.75
.Locked = True
.Placement = xlFreeFloating
.Enabled = True
' .Top = Target.Offset(-9).Top
.Top = Range(Cem).Offset(-9).Top 'vertical same for every column
.Left = Target.Offset(, -1).Left 'but horizontal relative to target
.Visible = bVisible
Stop
End With
End If 'r1 is nothing
Set r1 = Nothing
End Sub