Formula

[DVM]

Do you want a dialog to pop up EACH TIME the user selects a cell in column K? ....................YES
Do you want a dialog to pop up EACH TIME the user selects a cell in column N? ....................YES
Do you want a dialog to pop up EACH TIME the user selects a cell in column P? ....................YES
So, $K5=Concatenation of Answer 2 and Answer 1, similarly for $n5 and $P5

Or when the user double-clicks a cell in column K?.....................................................No
Or ...?

Thanks and best regards, Vilas Desai

[HansV]

Copy the userform frmSelect from the attached sample workbook into your workbook.

Sample.xlsm

Do you already have a Worksheet_SelectionChange event procedure for MasterList? If not, create the following procedure in the worksheet module for MasterList:

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Range("K5:K" & Rows.Count & ",N5:N" & _
            Rows.Count & ",P5:P" & Rows.Count), Target) Is Nothing Then
        frmSelect.Show
    End If
End Sub

If you already have Worksheet_SelectionChange, add the code (without Sub ... and End Sub) to the existing procedure.

[DVM]

please hold on

[HansV]

Uh... OK.

[DVM]

Yes, I could get it. I had actually missed out on integrating the frmselect user form into my workbook. It works good. Actually Under the selection "Enter Box Number = ...." the value entered should be only 3 digit Numerical and not alpha numeric. Thank you, best regards, Vilas Desai

[HansV]

That's different from what you originally specified, but you can add the following code to the module of frmSelect:

Code: Select all

Private Sub txtBoxNumber_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case vbKey0 To vbKey9
            ' Digits are allowed up to a maximum of 3
            If Len(Me.txtBoxNumber) >= 3 Then
                Beep
                KeyCode = 0
            End If
        Case vbKeyDelete, vbKeyBack, vbKeyLeft, vbKeyRight, vbKeyHome, vbKeyEnd, vbKeyTab, vbKeyReturn
            ' These keystrokes are allowed
        Case Else
            ' Other keystrokes are disabled
            Beep
            KeyCode = 0
    End Select
End Sub

[DVM]

Works perfect, thank a lot. Best regards, Vilas Desai

[DVM]

Dear Hans,

I tried to have three different user form contents for Cols K, N and P which works well. But when I visit the same cell which which already had values again, I get a run time error. Please advsie me where I am going wrong.
I am sending the original file where I made these changes.

Thanks and best regards

Vilas Desai
File sent through email please.

[HansV]

The problem with existing values is that the combo box items are now 5 characters instead of just 1 character. I have attached a version where this problem has been solved.
This version uses a single userform; the label captions are set in the UserForm_Initialize event procedure, based on the column of the active cell:

Code: Select all

Private Sub UserForm_Initialize()
    With Me.cboBoxType
        .AddItem "JB_A1"
        .AddItem "JB_A2"
        .AddItem "JB_B1"
        .AddItem "JB_B2"
    End With
    If Len(ActiveCell.Value) > 1 Then
        Me.cboBoxType = Right(ActiveCell, 5) ' the combo box items have length 5
        Me.txtBoxNumber = Left(ActiveCell, Len(ActiveCell) - 5)
    End If
    Select Case ActiveCell.Column
        Case 11 ' K
            Me.lblBoxType.Caption = "Box Type"
            Me.lblBoxNumber.Caption = "Box Number"
        Case 14 ' N
            Me.lblBoxType.Caption = "Marshalling Rack Type"
            Me.lblBoxNumber.Caption = "Marshalling Rack Number"
        Case 16 ' P
            Me.lblBoxType.Caption = "PLC Panel Type"
            Me.lblBoxNumber.Caption = "PLC Panel Number"
    End Select
End Sub

I have sent you the file by e-mail.

[DVM]

Hello Hans, Thanks for this. I had actually wnted to have seperate codes for different cols because:
1. I cannot have different elements in the drop down lists in the msg box
2. I can reuse the code for different cols myself
If you could please extend your courtesey to acommodate this request, that will help me.
Best regards
Vilas Desai

[HansV]

It's easy to use different elements in the dropdown lists. Change the code of the UserForm_Initialize event:

Code: Select all

Private Sub UserForm_Initialize()
    Select Case ActiveCell.Column
        Case 11 ' K
            Me.lblBoxType.Caption = "Box Type"
            Me.lblBoxNumber.Caption = "Box Number"
            With Me.cboBoxType
                .AddItem "JB_A1"
                .AddItem "JB_A2"
                .AddItem "JB_B1"
                .AddItem "JB_B2"
            End With
        Case 14 ' N
            Me.lblBoxType.Caption = "Marshalling Rack Type"
            Me.lblBoxNumber.Caption = "Marshalling Rack Number"
            With Me.cboBoxType
                .AddItem "This"
                .AddItem "That"
                .AddItem "Other"
            End With
        Case 16 ' P
            Me.lblBoxType.Caption = "PLC Panel Type"
            Me.lblBoxNumber.Caption = "PLC Panel Number"
            With Me.cboBoxType
                .AddItem "Red"
                .AddItem "Green"
                .AddItem "Blue"
                .AddItem "Yellow"
                .AddItem "Orange"
                .AddItem "Purple"
            End With
    End Select
End Sub

This is just an example, of course. You can change the items as needed. You can also add code for other columns, if desired.

[DVM]

Thank you so much. With this code, I could add a few more cols this way?

[HansV]

Yes, just add one or more new sections of the form

Code: Select all

        Case ... ' column number
            Me.lblBoxType.Caption = "..."
            Me.lblBoxNumber.Caption = "..."
            With Me.cboBoxType
                .AddItem "..."
                .AddItem "..."
                .AddItem "..."
                .AddItem "..."
            End With

Fill in the appropriate values.