Hello raghuprabhu
This may not be the most efficient way to do the coding, but it if you can learn from it then you may be able to develop a more efficient way to meet your requirements…
_1 ) I have put a Call to the routine , Sub DefaultItem , in the Sub Workbook_Open() in ThisWorkbook code module
( You could possibly replace this routine , Sub DefaultItem , with a formula, to achieve the same results. I do not have much experience with such formulas, but I think it should be possible )
ThisWorkbook Code Module.JPG : https://imgur.com/XPUlCTa
ThisWorkbook Code Module.JPG
Code:
Option Explicit
Public openFlag As Boolean ' This is to stop routine working on open
Private Sub Workbook_Open()
Call Sheet2.DefaultItem
UserForm1.Show
End Sub
This gives us our default items range
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Row\Col |
D |
E |
1 |
Group |
Deafault item |
2 |
1 |
ItemG11 |
3 |
2 |
ItemG21 |
4 |
3 |
ItemG31 |
Worksheet: Sheet2
_2) “…. say if I select "ItemG16", then I want it to input "ItmeG16" in column 2 and in column 3 input the comment "Instead of ItemG16 use ItemG11" …..”
Private Sub cboItem_Change() in Form, UserForm1
' a) when I select an item in the combo box for input in sheet1
This is the selected value from Form Userform1….
Private Sub cboItem_Change()
cboItm = cboItem.Value
' b) if the item is not in column 3 of sheet2
We can look for the position along column C in worksheet “Sheet2” of the item. If we do not find it. Then we…………
' c) input "ItemG16" in column 2
Put selected value in the next free row in column B, in Worksheet, “Sheet1”
' d) in column 3 input the comment "Instead of ItemG16 use ItemG11"
' d)(i) determine position along of our selected item down column B in Sheet2
' d) (ii) determine the group number for this item
' d)(iii) use group number in a VLookUp of our default items range to determine the default item number to use
' d)(iv) ".... in column 3 input the comment "Instead of ItemG16 use ItemG11" ......"
Paste in column C , worksheet “Sheet2” , we paste in like:
" Instead of " selected value " use " default item number
Code:
Private Sub cboItem_Change() ' ........say if I select "ItemG16", then I want it to input "ItmeG16" in column 2 and in column 3 input the comment "Instead of ItemG16 use ItemG11"
' Stop
If ThisWorkbook.openFlag = False Then: Let ThisWorkbook.openFlag = True: Exit Sub ' This is to stop routine working on open
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets("Sheet1"): Set Ws2 = ThisWorkbook.Worksheets("Sheet2")
' a) when I select an item in the combo box for input in sheet1
Dim cboItm As String: Let cboItm = cboItem.Value
' b) if the item is not in column 3 of sheet2
Dim ItemColB() As Variant: Let ItemColB() = Ws2.Range("C2:C" & Ws2.Range("C" & Rows.Count & "").End(xlUp).Row & "").Value
Dim MtchRes As Variant
Let MtchRes = Application.Match(cboItm, ItemColB(), 0) ' This will return a VBA error value if it cannot find the püosition along ( of cboItm , in array ItemColC() , looking for exact match )
If IsError(MtchRes) Then ' case if the item is not in column 3 of sheet2
' c) input "ItemG16" in column 2
Dim LastItem As Long: Let LastItem = Ws1.Range("B" & Rows.Count & "").End(xlUp).Row
Let Ws1.Range("B" & LastItem + 1 & "").Value = cboItm
' d) in column 3 input the comment "Instead of ItemG16 use ItemG11"
' d)(i) determine position along of our selected item down column B in Sheet2
Dim ItemColA() As Variant: Let ItemColA() = Ws2.Range("B2:B" & Ws2.Range("B" & Rows.Count & "").End(xlUp).Row & "").Value
Dim posItmColA As Long: Let posItmColA = Application.Match(cboItm, ItemColA(), 0)
' d)(ii) determine the group number for this item
Dim GrpNo As Long: Let GrpNo = Ws2.Range("A" & posItmColA).Value
Dim DefItms() As Variant: Let DefItms() = Ws2.Range("D1:E" & Ws2.Range("D" & Rows.Count & "").End(xlUp).Row).Value
' d)(iii) use group number in a VLookUp of our default items range to determine the deafault item number to use
Dim DefItm As String: Let DefItm = Application.WorksheetFunction.VLookup(CStr(GrpNo), DefItms(), 2, 0)
'Dim Item() As Variant: Let Item() = Ws1.Range("B2:B" & LastItem & "").Value
' d)(iv) ".... in column 3 input the comment "Instead of ItemG16 use ItemG11" ......"
Let Ws1.Range("C" & LastItem + 1 & "").Value = " Instead of " & cboItm & " use " & DefItm & ""
Else ' case the item is not in column 3 of sheet2
End If
End Sub
Example in next post
Bookmarks