hippy
06-19-2008, 02:37 PM
I am aware of the vba code at CONTEXTURES.COM; saddly I am unable to get the code to work as I understand it, which is disappointing becasue it is precisely what I am after.
I have written the code below which works and would meet my business needs. The one thing I can not manage is to have the combobox.visibility = false, after a selection from the dropdown list, with out having to move out of the intersect, can this be achieved?
I would appreciate some advise; thank you in advance.
Hippy
Dim strCmb1Val As String
Dim cmb1box As r OLEObject
Dim varRangeTarget As Variant
Public varCurAdd As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varCurAdd = ActiveCell.Address
If Intersect (ActiveCell, Range("G3:G65365")) Is Nothing Then
Me.OLEObjects("cmb1").Visible = False
Cmb1.ListFillRange = ""
Else
Cmb1.Value = ""
Me.OLEObjects("cmb1").Visible = True
With Worksheets("NON-EDIS EDs")
.Cmb1.Left = Target.Left
.Cmb1.Top = Target.Top + Target.Height
.Cmb1.Width = Target.Width * 1.03
.Cmb1.Height = Target.Height * 1.5
.Cmb1.FontSize = 12
End With
End If
End Sub
Private Sub Cmb1_DropButtonClick()
Cmb1.ListFillRange = "NON_Dep_Stat"
ActiveCell = varCurAdd
ActiveCell.Value = Cmb1.Value
Cmb1.Value = ""
ActiveCell.Activate
End Sub
I have written the code below which works and would meet my business needs. The one thing I can not manage is to have the combobox.visibility = false, after a selection from the dropdown list, with out having to move out of the intersect, can this be achieved?
I would appreciate some advise; thank you in advance.
Hippy
Dim strCmb1Val As String
Dim cmb1box As r OLEObject
Dim varRangeTarget As Variant
Public varCurAdd As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varCurAdd = ActiveCell.Address
If Intersect (ActiveCell, Range("G3:G65365")) Is Nothing Then
Me.OLEObjects("cmb1").Visible = False
Cmb1.ListFillRange = ""
Else
Cmb1.Value = ""
Me.OLEObjects("cmb1").Visible = True
With Worksheets("NON-EDIS EDs")
.Cmb1.Left = Target.Left
.Cmb1.Top = Target.Top + Target.Height
.Cmb1.Width = Target.Width * 1.03
.Cmb1.Height = Target.Height * 1.5
.Cmb1.FontSize = 12
End With
End If
End Sub
Private Sub Cmb1_DropButtonClick()
Cmb1.ListFillRange = "NON_Dep_Stat"
ActiveCell = varCurAdd
ActiveCell.Value = Cmb1.Value
Cmb1.Value = ""
ActiveCell.Activate
End Sub