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.

[TABLE="class: cms_table_cms_table_cms_table_grid, width: 486"]
<tbody>[TR]
[TD]Subject Name[/TD]
[TD]Type[/TD]
[TD]Res[/TD]
[TD]Appointment[/TD]
[TD]ID[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DM New[/TD]
[TD]0.30[/TD]
[TD]Confirmed[/TD]
[TD]15605[/TD]
[TD]8:45 AM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[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