List/Combo box

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
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...
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

Active Member
Joined
Aug 20, 2009
Messages
278

ADVERTISEMENT

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-known Member
Joined
Sep 23, 2005
Messages
886
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

Active Member
Joined
Aug 20, 2009
Messages
278

ADVERTISEMENT

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

Well-known Member
Joined
Sep 23, 2005
Messages
886
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
AB
1
2TypeReturn
3StatusTRUE
4Incident TypeTRUE
5PriorityTRUE
6ApplicationFALSE
7ComponentTRUE
8Target DateFALSE
9Assigned ToTRUE
Sheet1
Excel 2003

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

Code:
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:~

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

Active Member
Joined
Aug 20, 2009
Messages
278
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...
 

Forum statistics

Threads
1,144,568
Messages
5,725,038
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top