I have a combo box in Excel that is used to offer a user a choice of documents to publish to the web; the ListFillRange is a named range on another sheet. The procedure is: the user is to select a job title, click OK on a confirmation message box, and the appropriate job is run. So far, so good.
However, when I click on an item in the comb o-box list, the message box pops up as it should, but the combo box remains expanded and active behind the message box, so the user can at that point select a job other than the one named in the message box.
The combo box is style 2, dropdownlist, but the same thing happens rerardless of which style I use. In addition, though I try to disable the combo box before the message box is called, it doesn’t help. Here is the code I’m using (slightly truncated):
Private Sub cboJobDescriptions_Change()
Dim strMsg As String
Dim strJobDesc
Dim strJobNum As String
strMsg = “Do you want to publish ”
strMsg = strMsg & cboJobDescriptions.Text
strMsg = strMsg & ” to the Web?”
Select Case cboJobDescriptions.ListIndex
Case 0
Case Else
If MsgBox(strMsg, vbOKCancel + vbQuestion + vbApplicationModal, “Publish to Web?”) = vbOK Then
strJobDesc = cboJobDescriptions.Text
strJobNum = WorksheetFunction.VLookup _(strJobDesc, Sheets(“Sheet2”).Range _(“Job_Numbers”), 2, 0)
Call Publish(strJobNum) _
cboJobDescriptions.Visible = False _
cboJobDescriptions.Enabled = False
End If
End Select
End Sub
Any and all help appreciated.
