PC Review


Reply
Thread Tools Rate Thread

Conditional automatic entry from a list using VBA

 
 
Syd
Guest
Posts: n/a
 
      28th Dec 2006
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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatic conditional formatting from a list BeSmart Microsoft Excel Programming 0 5th Mar 2010 11:42 AM
LIST ENTRY >> ENABLE DROP DOWN LIST TO ACCEPT MORE THAN 1 ENTRY Frank Microsoft Excel Misc 2 18th Sep 2008 10:31 PM
in a form automatic entry in a text box by entry other field valu ihsan Microsoft Access Forms 1 9th Sep 2008 09:27 PM
Re: How to remove an entry from the automatic completion list? Jocelyn Fiorello [MVP - Outlook] Microsoft Outlook 0 5th Aug 2003 02:25 AM
How to remove an entry from the automatic completion list? Howardz Microsoft Outlook Discussion 0 4th Aug 2003 04:33 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:30 AM.