ExcelBanter - Help required with VBA program to automatically enter data from a validation list.

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Help required with VBA program to automatically enter data from a validation list. (https://www.excelbanter.com/excel-programming/380809-help-required-vba-program-automatically-enter-data-validation-list.html)

Help required with VBA program to automatically enter data from a validation list.

Can somone please help me with this program.

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 a Kit is selected from the drop down validation list I would like my
VBA program to populate the column with the individual tools which make up
the Kit.

Can someone tell me why my VBA program below requires two extra clicks to do
this after selecting a Kit from the drop down validation list..

Assuming the current cell is C10

I select a kit from the validation list the description (Eg. Kit1) appears
in cell C10 as Kit1 and not the individual tools that make up the Kit1.
I have to click on the empty cell C11 below and then again on cell C10
(Kit1)and only then will it populate 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

Help required with VBA program to automatically enter data from a validation list.

The :
Sub Worksheet_SelectionChange(By Val Target as range

You may be bettet to try the :
Sub WorkSheet Activate
- event instead.

Corey....

ONLY ecognises a change once the cell or range is No longer actvated.
"Syd" wrote in message
...
Can somone please help me with this program.

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 a Kit is selected from the drop down validation list I would like my
VBA program to populate the column with the individual tools which make up
the Kit.

Can someone tell me why my VBA program below requires two extra clicks to
do this after selecting a Kit from the drop down validation list..

Assuming the current cell is C10

I select a kit from the validation list the description (Eg. Kit1) appears
in cell C10 as Kit1 and not the individual tools that make up the Kit1.
I have to click on the empty cell C11 below and then again on cell C10
(Kit1)and only then will it populate 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

Help required with VBA program to automatically enter data from a validation list.

Try the Change event, not selectionchange, and test that it is the DV cell
being changed

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H5" '<=== DV cell - change to suit
Dim CodeRow As Integer
Dim CodeCol As Integer
Dim Count As Integer
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "Kit 1": Call Kit1
Case "Kit 2": 'Call Kit2
Case Else
End Select
End If
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Syd" wrote in message
...
Can somone please help me with this program.

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 a Kit is selected from the drop down validation list I would like my
VBA program to populate the column with the individual tools which make up
the Kit.

Can someone tell me why my VBA program below requires two extra clicks to
do this after selecting a Kit from the drop down validation list..

Assuming the current cell is C10

I select a kit from the validation list the description (Eg. Kit1) appears
in cell C10 as Kit1 and not the individual tools that make up the Kit1.
I have to click on the empty cell C11 below and then again on cell C10
(Kit1)and only then will it populate 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

Help required with VBA program to automatically enter data from a validation list.

Sorry forgot a bit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H5" '<=== DV cell - change to suit
Dim CodeRow As Integer
Dim CodeCol As Integer
Dim Count As Integer
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "Kit 1": Call Kit1
Case "Kit 2": 'Call Kit2
Case Else
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Bob Phillips" wrote in message
...
Try the Change event, not selectionchange, and test that it is the DV cell
being changed

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H5" '<=== DV cell - change to suit
Dim CodeRow As Integer
Dim CodeCol As Integer
Dim Count As Integer
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "Kit 1": Call Kit1
Case "Kit 2": 'Call Kit2
Case Else
End Select
End If
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Syd" wrote in message
...
Can somone please help me with this program.

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 a Kit is selected from the drop down validation list I would like
my VBA program to populate the column with the individual tools which
make up the Kit.

Can someone tell me why my VBA program below requires two extra clicks to
do this after selecting a Kit from the drop down validation list..

Assuming the current cell is C10

I select a kit from the validation list the description (Eg. Kit1)
appears in cell C10 as Kit1 and not the individual tools that make up
the Kit1.
I have to click on the empty cell C11 below and then again on cell C10
(Kit1)and only then will it populate 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

Help required with VBA program to automatically enter data from a validation list.

Hi

I am new to VBA.

Can you explain what you mean by "DV cell"

And the line "If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then"

Thanks

Syd

"Bob Phillips" wrote in message
...
Sorry forgot a bit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H5" '<=== DV cell - change to suit
Dim CodeRow As Integer
Dim CodeCol As Integer
Dim Count As Integer
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "Kit 1": Call Kit1
Case "Kit 2": 'Call Kit2
Case Else
End Select
End If

ws_exit:
Application.EnableEvents = True
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Bob Phillips" wrote in message
...
Try the Change event, not selectionchange, and test that it is the DV
cell being changed

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H5" '<=== DV cell - change to suit
Dim CodeRow As Integer
Dim CodeCol As Integer
Dim Count As Integer
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Select Case Target.Value
Case "Kit 1": Call Kit1
Case "Kit 2": 'Call Kit2
Case Else
End Select
End If
End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"Syd" wrote in message
...
Can somone please help me with this program.

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 a Kit is selected from the drop down validation list I would like
my VBA program to populate the column with the individual tools which
make up the Kit.

Can someone tell me why my VBA program below requires two extra clicks
to do this after selecting a Kit from the drop down validation list..

Assuming the current cell is C10

I select a kit from the validation list the description (Eg. Kit1)
appears in cell C10 as Kit1 and not the individual tools that make up
the Kit1.
I have to click on the empty cell C11 below and then again on cell C10
(Kit1)and only then will it populate 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