List/Combo box

[Rocky0201]

If I were to create a list/combo box with the items:

<TABLE style="WIDTH: 166pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=221 border=0><COLGROUP><COL style="WIDTH: 166pt" width=221><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="WIDTH: 166pt; HEIGHT: 15pt" width=221 height=20>Status</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>Incident Type</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>Priority</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>Application</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>Component</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="HEIGHT: 15pt" height=20>Target Date</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>Assigned To</TD></TR></TBODY></TABLE>
How do I determine which of the above selection was made?

Thanks...

 

[Airfix9]

Use the value property of the list/combo box.

 

[Rocky0201]

Thanks AirFix9.

Can you give me and example?

Thanks again...

 

[Airfix9]

It would help if you could tell me what you want to do with it. Also, is this a combobox on a userform or a control directly on the worksheet? An example of what you have already would help a great deal.

Ta.

 

[Rocky0201]

Thanks for getting back to me AirFix9...

I have a User Form (UserForm2) that has 2 Option boxes; one is defeined as Sort Direction A to Z and the other defined as Sort Direction Z to A.

I also have 2 Command Button's; one defined as OK and the other defined as Cancel.

I also have 7 other Command Button's that each one represents a name that I defined in my first post. What I was looking to do is have the ability inhibit any of the 7 Command Button's depending on which cell is Double Clicked from my Sheet 1.

It was suggested that a better approach than using the 7 Command Button's would be to use a List or Combo box. I can modify my code to recognize a List or Combo box (if this makes more sense than using the Command Button's) but I do not know what to code if one of the 7 selections were to be made by the user.

So far, everything that I have coded using the User Form (with the Command Button's) works fine. I just need to know how to inhibit a button within my code.

Hope this makes sense.. if not, please lt me know.

Thanks...

 

[Airfix9]

Well, if you are talking about disabling command buttons then you need to change their Enabled property to False in the UserForm_Initialize event. If you are asking for a double-click on a worksheet to enable/disable the userform's command button, I would set up a worksheet event that populates a True/False list which would be used to set up the userform.

Hope that makes sense.

 

[Rocky0201]

Very cool Airfix9... Thanks...

How can I communicate to UserForm_Initial (in my UserForm2 area) that an cell was double clicked from my Worksheet_BeforeDoubleClick area (coded in Sheet1)?

In Sheet1 --

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'------------------------------------------------------
'-- User Selected to Display Report (All Incidents)
'------------------------------------------------------
If Target.Column = 1 And Target.Row = 2 Then
UserForm2.Show
End If
End Sub

In UserForm2 --

Private Sub UserForm_Initialize()
Me.OptionButton1 = True
End Sub

Thanks Airfix9...

 

[Airfix9]

This is how I solve it. I use sequentially numbered command buttons, it really does not matter about their names except from a programming point of view, so numbers are the best way to deal with it.

So, I have the List in Sheet1 as follows:~

Excel Workbook
	A	B
1
2	Type	Return
3	Status	TRUE
4	Incident Type	TRUE
5	Priority	TRUE
6	Application	FALSE
7	Component	TRUE
8	Target Date	FALSE
9	Assigned To	TRUE
Sheet1

Excel 2003

Then I place this code in the sheet module for sheet1:~

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Target.Column<> 1 Then Exit Sub
    If Target.Row< 3 Then Exit Sub
    If Target.Row > 9 Then Exit Sub
    
    
    If Not Intersect(Target, Range("A3:A9")) Is Nothing Then
    
        Application.EnableEvents = False
        
        If Target.Offset(, 1) = False Then
            Target.Offset(, 1) = True
        Else
            Target.Offset(, 1) = False
        End If
        
        Application.EnableEvents = True
    
    End If
    
End Sub

I then add my buttons, which I have called "CommandButton1", "CommandButton2" etc up to "CommandButton7" to my userform and, in the UserForm_Initialize event, I place this code:~

Private Sub UserForm_Initialize()

    Dim C As Control, i As Integer
    
    With ThisWorkbook.Sheets("Sheet1")
    
        For i = 1 To 7
            
            Me.Controls("CommandButton" & i).Enabled = .Cells(i + 2, 2).Value
        
        Next
        
    
    End With


End Sub

When you launch the userform, you will then get your command buttons enabled and disabled as per the True/False list.

Hope that helps.

 

[Rocky0201]

Thanks Airfix9....

This DOES help!!!

I wish there was a way for the double click event to pass to the user form event a paramater that I can set in the double click event but if your suggestion is the way around it, then so be it...

Thanks so much Airfix9...

 

[Airfix9]

You are most welcome.