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
Formula
-
- Administrator
- Posts: 78629
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula
Copy the userform frmSelect from the attached sample workbook into your workbook.
Do you already have a Worksheet_SelectionChange event procedure for MasterList? If not, create the following procedure in the worksheet module for MasterList:
If you already have Worksheet_SelectionChange, add the code (without Sub ... and End Sub) to the existing procedure.
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78629
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: Formula
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
-
- Administrator
- Posts: 78629
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: Formula
Works perfect, thank a lot. Best regards, Vilas Desai
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: Formula
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.
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.
-
- Administrator
- Posts: 78629
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula
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:
I have sent you the file by e-mail.
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: Formula
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
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
-
- Administrator
- Posts: 78629
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula
It's easy to use different elements in the dropdown lists. Change the code of the UserForm_Initialize event:
This is just an example, of course. You can change the items as needed. You can also add code for other columns, if desired.
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
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 110
- Joined: 19 Jul 2014, 08:26
Re: Formula
Thank you so much. With this code, I could add a few more cols this way?
-
- Administrator
- Posts: 78629
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula
Yes, just add one or more new sections of the form
Fill in the appropriate values.
Code: Select all
Case ... ' column number
Me.lblBoxType.Caption = "..."
Me.lblBoxNumber.Caption = "..."
With Me.cboBoxType
.AddItem "..."
.AddItem "..."
.AddItem "..."
.AddItem "..."
End With
Best wishes,
Hans
Hans