Printable View
Hi All,
I have a small problem.
In sheet2 of the attached file, I have in column1 have group, column2 has items, column3 also has the same items, some of them missing.
When I select an item in the form from the dropdown list, I want the comment to be populated as shown. If the item selected from column2 is from group1 and is not in column3 then I want the comments column to be as shown.
I need some help to achieve this.
Thanks
Raghu Prabhu
Hello raghuprabhu
Welcome to excelfox
I do not fully understand your question.
For example you say … When I select an item in the form from the dropdown list …. ??
?? - I do not see any drop down list in your file.
For now I will just give you some ideas that may help you to go further yourself.
If you then still need some help, then I think you will need to explain a bit better exactly what you want.
If you or nobody else can help solve your problem, then I can take another look for you tomorrow:
I only have a little time now to give you some ideas… if no one else helps, and you still need help tomorrow, then please give more information about what you want, so that I can help further tomorrow
This is the test range that you have given: ( I have also enclosed your CurrentRegion
as given, for example by Range("A1").CurrentRegion )
_____ Workbook: Lists.xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet2
Row\Col ItemG11 ItemG11 ItemG12 ItemG12 ItemG13 ItemG13 ItemG14 ItemG14 ItemG15 ItemG16 ItemG17 ItemG18 ItemG19 ItemG21 ItemG21 ItemG22 ItemG22 ItemG23 ItemG23 ItemG24 ItemG24 ItemG25 ItemG26 ItemG27 ItemG28 ItemG29 ItemG31 ItemG31 ItemG32 ItemG32 ItemG33 ItemG33 ItemG34 ItemG34 ItemG35 ItemG36 ItemG37 ItemG38
You wish to somehow get this sort of output , based on the above test data:
_____ Workbook: Lists.xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col Raghu ItemG11 Vim ItemG16 Instead of ItemG16 use ItemG11 Abhi ItemG21 Bhanu ItemG25 Instead of ItemG25 use ItemG21 John ItemG34 Vicky ItemG35 Instead of ItemG35 use ItemG31
This is your explanation so far…._
_............... In sheet2 of the attached file, I have in column1 have group, column2 has items, column3 also has the same items, some of them missing.
When I select an item in the form from the dropdown list, I want the comment to be populated as shown. If the item selected from column2 is from group1 and is not in column3 then I want the comments column to be as shown.
In the next post I will give you some ideas….
These are just some ideas to get you started.
I will give you a routine which will give you an array, arrOut() , like this
The routine is here:Code:Group Deafault item
1 ItemG11
2 ItemG21
3 ItemG31
http://www.excelfox.com/forum/showth...ll=1#post11094
Routine brief description
Rem 1
I get the current data region to which includes the first cell in worksheet “Sheet2”
Rem 2
I make an array, arrDtaIn() , of the data
1 ItemG11 ItemG11 1 ItemG12 ItemG12 1 ItemG13 ItemG13 1 ItemG14 ItemG14 1 ItemG15 1 ItemG16 1 ItemG17 1 ItemG18 1 ItemG19 2 ItemG21 ItemG21 2 ItemG22 ItemG22 2 ItemG23 ItemG23 2 ItemG24 ItemG24 2 ItemG25 2 ItemG26 2 ItemG27 2 ItemG28 2 ItemG29 3 ItemG31 ItemG31 3 ItemG32 ItemG32 3 ItemG33 ItemG33 3 ItemG34 ItemG34 3 ItemG35 3 ItemG36 3 ItemG37 3 ItemG38
Rem 3
' 3a)
I make a string of the unique group numbers “ 1 2 3 “
' 3b)
I make an array of the unique group numbers { 1, 2, 3 }
' 3c) - ' 3d)
I make and fill an array for output, arrOut()
Rem 4
I paste out that array for output, arrOut()
Results
For this input data, before
_____ Workbook: Lists.xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet2
Row\Col ItemG11 ItemG11 ItemG12 ItemG12 ItemG13 ItemG13 ItemG14 ItemG14 ItemG15 ItemG16 ItemG17 ItemG18 ItemG19 ItemG21 ItemG21 ItemG22 ItemG22 ItemG23 ItemG23 ItemG24 ItemG24 ItemG25 ItemG26 ItemG27 ItemG28 ItemG29 ItemG31 ItemG31 ItemG32 ItemG32 ItemG33 ItemG33 ItemG34 ItemG34 ItemG35 ItemG36 ItemG37 ItemG38
Now run routine, Sub DefaultItem() ( http://www.excelfox.com/forum/showth...ll=1#post11094 )
After running Sub DefaultItem()
_____ Workbook: Lists.xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet2
Row\Col Group Deafault item ItemG11 ItemG11 1 ItemG11 ItemG12 ItemG12 2 ItemG21 ItemG13 ItemG13 3 ItemG31 ItemG14 ItemG14 ItemG15 ItemG16 ItemG17 ItemG18 ItemG19 ItemG21 ItemG21 ItemG22 ItemG22 ItemG23 ItemG23 ItemG24 ItemG24 ItemG25 ItemG26 ItemG27 ItemG28 ItemG29 ItemG31 ItemG31 ItemG32 ItemG32 ItemG33 ItemG33 ItemG34 ItemG34 ItemG35 ItemG36 ItemG37 ItemG38
So at the end of the routine you have an arrary, arrOut() ,which looks like this
arrOut() =
Group Deafault item 1 ItemG11 2 ItemG21 3 ItemG31
Select ArrOut then Hit F9.JPG : https://imgur.com/zC6Lin4
Attachment 2216
If you need more help from me tomorrow, then you must give me more infomation... This : ...... When I select an item in the form from the dropdown list, ... does not suit the file you gave ... or are you refering to the Form instance of UserForm1 ?? Please be more specific. Please tell me exactly what should happen and when it should happen
Alan
Ref
http://www.excelfox.com/forum/showth...lication-Index
Hi DocAElstein
My apologies. I did not put the code to open the form on opening the workbook.
When do I call the Sub DefaultItem?
Kind regards
Raghu
Hello raghuprabhu
Sub DefaultItem was not intended to be a final solution to solve your problem.
I still do not fully understand your requirement. So I cannot give a full solution, until I understand exactly what you want
So yesterday, I wrote Sub DefaultItem to help give you some basic ideas. If you try to understand how Sub DefaultItem works, then you may be able to get further yourself.
I do not have much experience with UserForm Forms. But I may revise some of my knowledge on them later today or tomorrow.
If you want me to try to help you further, it would be helpful to me if you do the following in the meantime
Please do this:
_1) Include the code to open the UserForm on opening the workbook.
_2) Please try again to explain clearly want you want.
Hi Alan,
There is a form included in the attached workbook.
The source list for the combo box is column 2 in sheet2
when I select an item in the combo box for input in sheet1, if the item is not in column 3 of sheet2, 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"
Alan Thanks for your input
Kind regards
Raghu
PS How do I attach the file the second time?
Hi,Quote:
Originally Posted by raghuprabhu
Uploading file a second time.......
_1) You may need to “Go Advanced”
The normal Start point is with the Paper Clip icon at the top of the Editor Window. That icon will be present in the initial Post Editor, but it may not always be available for further Replys. You may need first to “Go Advanced”
GoAdvancedReplyWindow.JPG : GoAdvanced1.JPG https://imgur.com/1A9qWQM : https://imgur.com/UXBZ4oJ
Attachment 2217Attachment 2218
_2) _3) Hit Paper Clip and Add Files
PaperClip2AddFiles3.JPG
https://imgur.com/vbPQvTr
_4) Select Files
SelectFiles.JPG
https://imgur.com/aqtVTPa
_5) Upload Files
UploadFiles5.JPG
https://imgur.com/pUfmZc7
_6) Hit Done
Done6.JPG
https://imgur.com/kQAwzao
_-.----
You can also get thereabouts with the Manage Attachments option which you will see when youscroll down after hitting “Go Advanced”
ManageAttachments.JPG
https://imgur.com/KxTxRoC
_.------
( To delete Files:
DeleteAttachment.JPG
https://imgur.com/STzpq9E
_..
You can practice posting here:
http://www.excelfox.com/forum/forumd...p/17-Test-Area
Start a new Thread, with a title such as “Just testing and practicing posting, no reply needed”
You can do anything you want, post anything, Edit, and try again etc… etc.. )
Alan
Hi Alan,
I have uploaded the workbook with the form opening code.
Thank you for your help.
Kind regards
Raghu
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
Attachment 2222
This gives us our default items rangeCode: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
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet2
Row\Col
_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
Example for …._
_.. this data in ..
Sheet 2
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet2
Row\Col ItemG11 ItemG11 ItemG12 ItemG12 ItemG13 ItemG13 ItemG14 ItemG14 ItemG15 ItemG16 ItemG17 ItemG18 ItemG19 ItemG21 ItemG21 ItemG22 ItemG22 ItemG23 ItemG23 ItemG24 ItemG24 ItemG25 ItemG26 ItemG27 ItemG28 ItemG29 ItemG31 ItemG31 ItemG32 ItemG32 ItemG33 ItemG33 ItemG34 ItemG34 ItemG35 ItemG36 ItemG37 ItemG38
Sheet 1 Before
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col Alan ItemG16 Instead of ItemG16 use ItemG11 Abhi ItemG21 Bhanu ItemG25 Instead of ItemG25 use ItemG21 John ItemG34 Vicky ItemG35 Instead of ItemG35 use ItemG31 Vim ItemG18
Now select from drop down box , Item 24
Item24.JPG : https://imgur.com/BDsmk3A
Attachment 2221
After : ( no change )
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col Alan ItemG16 Instead of ItemG16 use ItemG11 Abhi ItemG21 Bhanu ItemG25 Instead of ItemG25 use ItemG21 John ItemG34 Vicky ItemG35 Instead of ItemG35 use ItemG31 Vim ItemG18
Now select from drop down box , Item 25
Item25.JPG : https://imgur.com/fyPtICC
Attachment 2220
After:
_____ Workbook: ListsWithFormOpening.xlsm ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col Alan ItemG16 Instead of ItemG16 use ItemG11 Abhi ItemG21 Bhanu ItemG25 Instead of ItemG25 use ItemG21 John ItemG34 Vicky ItemG35 Instead of ItemG35 use ItemG31 Vim ItemG18 ItemG25 Instead of ItemG25 use ItemG21
Alan