Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional automatic entry from a list using VBA
I enter Tool Descriptions in Column 3 in my spreadsheet by selection from a DropDown Validation list.
I also have various combinations of the tools which I select from the list as Kit1, Kit2 ..........etc. When selecting a Kit from a drop down validation list I have a VBA program which populates the the column with the individual tool descriptions which make up the Kit. When I select a kit from the validation list the description (Eg. Kit1) appears in the relevant cell and not the various descriptions that make up the Kit1. If I then click on the empty cell below and then again on the cell above (Kit1) it populates the cells with the tools correctly. How do I get the cells populated automatically as required without having to click twice as described above? I use a case statement for the different kits which calls the relevant sub programs as follows: Sub Worksheet_SelectionChange(By Val Target as range Dim CodeRow As Interger Dim CodeCol As Interger Dim Count As Interger Select Case Target.Value Case "Kit 1" Call Kit1 Case"Kit 2" Call Kit2 Case Else End Select End Sub I then have Private Sub Programs for each Case. (Kit) The Tool Descriptions are located in a Table in Column 10 Example for Kit1. Private Sub Kit1() Dim ToolDescription Dim ContentsRow As Integer Dim ContentsCol as Integer ContentsCol = 10 CodeCol = 3 CodeRow = ActiveCell.Row (Where the Tool Description is to be entered) Contents Row = 19 (First Tool Description for Kit1 is located in the table in Col10 Row 19) ToolDescription = Cells(ContentsRow,ContentsCol).Value Cells(CodeRow,CodeCol).Value = ToolDescription CodeRow = CodeRow + 1 (Increments to next Row) Contents Row = 20 ToolDescription = Cells(ContentsRow,ContentsCol).Value Cells(CodeRow,CodeCol).Value = ItemDescription CodeRow = CodeRow + 1 Contents Row = 21 ItemDescription = Cells(ContentsRow,ContentsCol).Value Cells(CodeRow,CodeCol).Value = ToolDescription CodeRow = CodeRow + 1 End Sub Thanks for the anticipated help. Syd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LIST ENTRY ENABLE DROP DOWN LIST TO ACCEPT MORE THAN 1 ENTRY | Excel Discussion (Misc queries) | |||
How do I set up entry box to auto-alphabatize each entry in list? | Excel Discussion (Misc queries) | |||
Automatic Entry | Excel Discussion (Misc queries) | |||
Automatic Data Entry | Excel Discussion (Misc queries) | |||
Automatic cell entry | Excel Discussion (Misc queries) |