PDA

View Full Version : Combobox Visibility After Drop List Selection



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

david000
06-19-2008, 07:25 PM
Are you trying to change lists also as part of this project?

Here's an example that -- I think -- will choose the correct list from whatever column you're in.

see if it works for you better.

And what is this for if you don't mind?

david000
06-19-2008, 07:35 PM
Oh yeah,

and if it's the fact that you really don't want it to print use the --
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Range("a1").Select 'out of intersect
End Sub

hippy
06-19-2008, 09:56 PM
Hello david. I thought as you say simply use code to select another cell; I had a lapse in focus and simply could not see the way forward. Tehn while driving in the car a penny moment.

Thank you for replying on that one.

You asked for what purpose is the workbook used; it will will distributed to a number operational entities for addition of specific operational data within the financial year, htough the fields and Headers are not representative. The work books are returned and data consolidated in one file.

The data in the single file is in combination with fees and charges for estimating a financial position, and ultimately used to redaw funds from other departments and divisions; if that is not too abstract.

The reason for approaching the combobox in this way and not data validation, is now more out of interest, while still providing control over integrgity of required responses.

Thanks again.

Happy Day [or night]

mikerickson
06-19-2008, 10:00 PM
This will make the combobox not visible once something has been selected
Private Sub Cmb1_DropButtonClick()
Cmb1.ListFillRange = "NON_Dep_Stat"
ActiveCell = varCurAdd: Rem Mark 1
ActiveCell.Value = Cmb1.Value
Cmb1.Value = ""
Cmb1.Visible = False
ActiveCell.Activate: Rem Mark 2
End Sub

I am curious what the two marked lines are supposed to do.
The first marked line seems superfluous since the next line again writes to ActiveCell.

And ActiveCell.Activate ??

hippy
06-20-2008, 02:17 AM
More than likely an oversight on my part. I had commented our bits of code that I no longer required as I developed the code. I had made an attempt to remove unwanted code.

Hippy