VBA Excel Form

[Vani11]

[h=2]Re: Restrict entry on certain cells based on multiple drop drop-down list[/h]******** id="google_ads_iframe_/22152718/MrExcel_InContent_0" title="3rd party ad content" name="google_ads_iframe_/22152718/MrExcel_InContent_0" width="300" height="250" scrolling="no" marginwidth="0" marginheight="0" frameborder="0" style="border-width: 0px; border-style: initial; vertical-align: bottom;">*********>

Hi,

Would there be a way to link a dropdown list value to a user form? The appointment column "D" has drop-down list.

The drop-down list values are:
Confirmed
Rescheduled
Cancelled

If a user selects, "Rescheduled", a pop up form should appear to users to select the following checkboxes:

*Blood Test *Urinalysis * Blood Pressure

Next, when the user mouse over that row of data where the appointment is "rescheduled", it should show the checkbox which the user have selected.

Subject Name	Type	Res	Appointment	ID	Time
	DM New	0.30	Confirmed	15605	8:45 AM
		0.00
		0.00
		0.00
		0.00
		0.00
		0.00
		0.00

<tbody>
</tbody>

​

 

[wideboydixon]

OK. Here's what I did.

I added a new UserForm with three checkboxes and a button. The checkboxes have captions of "Blood Test", "Urinalysis" and "Blood Pressure". The button is called "OkButton", has a caption of "OK" and has the following code associated with clicking it:

Private Sub OkButton_Click()

Me.Hide

End Sub

I called the form "RescheduledForm" and gave it a caption of "Rescheduled".

I then added a Worksheet_Change event handler for the sheet with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim commentText As String
Dim check As Control

If Target.Count = 1 And Target.Column = 4 Then
    Target.ClearComments
    If Target.Value = "Rescheduled" Then
        RescheduledForm.Show vbModal
        commentText = ""
        For Each check In RescheduledForm.Controls
            If TypeName(check) = "CheckBox" Then
                If check.Value Then
                    commentText = commentText & IIf(commentText = "", "", ", ") & check.Caption
                End If
            End If
        Next check
        If commentText <> "" Then Target.AddComment commentText
        Unload RescheduledForm
    End If
End If

End Sub

When the user changes a cell in column D to have the value "Rescheduled", the form is shown and the checkboxes can be selected before clicking OK. This then adds a comment to that cell so that when you mouse over it, you can see the checked values. Changing a cell in column D to anything other than "Rescheduled" clears the comments on the cell.

WBD

 

[Vani11]

Hi there!

Thanks you so much!

Would these codes work across all other columns indicated with Appointment?

Because columns with the header, "Appointment" already has drop-down list.

Vani

 

[wideboydixon]

To make it work on cells in any "Appointment" column then perhaps change this:

If Target.Count = 1 And Target.Column = 4 Then

To this:

If Target.Count = 1 And Target.Row > 1 And Cells(1, Target.Column).Value = "Appointment" Then

WBD