Web Development

Ask something
Discussion Locked

Excel combo box expanded after change

0 helpful
3 replies

by ds_davis

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

3 replies

by haris

The following solution works by noting the value that is selected at the time the message box is displayed and comparing it with the value that is selected in the combo at the time Ok is pressed on the message box. The ReProcessHere: is used to re-show the message box. Your original code will also use the latest selected values in any case.

Dim strMsg As String
Dim strJobDesc
Dim strJobNum As String
dim OriginalComboValue as string

ReProcessHere:
strMsg = "Do you want to publish "
strMsg = strMsg & cboJobDescriptions.Text
strMsg = strMsg & " to the Web?"

OriginalComboValue=cboJobDescriptions.Text

Select Case cboJobDescriptions.ListIndex
Case 0

Case Else
If MsgBox(strMsg, vbOKCancel + vbQuestion + vbApplicationModal, "Publish to Web?") = vbOK Then

If OriginalComboValue=cboJobDescriptions.Text
strJobDesc = cboJobDescriptions.Text
strJobNum = WorksheetFunction.VLookup _(strJobDesc, Sheets("Sheet2").Range _("Job_Numbers"), 2, 0)
Call Publish(strJobNum) _
cboJobDescriptions.Visible = False _
cboJobDescriptions.Enabled = False
else
'Do here what is requied if the value is diffeent
GoTo ReProcessHere
End If
End Select

End Sub

by ds_davis

Actually, I was expecting to find a method for collapsing and disabling the combo box before the message box appeared.

However, after working with this solution a bit, I believe it is even better, as it provides a last-chance opportunity for the user to change his selection before processing.

Thank you

This discussion is closed to new replies.