Excel VBA code for populating a cell based on inout in another cell 1

[MISMonkey]

I have a list of values that appear in column A. If a user enters a specific code in column A I want the adjacent 4 columns to pre-fill with a fixed value (different value for each).

I'm having trouble trying to work this in such a dynamic way - as a user enters the trigger code in A4 I want rows B4, C4 D4 & E4 to be updated.

For simplicicy the trigger code is "Default" and the fixed values would be B4=1, C4=2 D4=3 & E4=4

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[xlbo]

Use the Worksheet_CHANGE event

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=green]'test to see if the change occured in Col A[/color]
if target.Column <> 1 then exit sub
application.enableevents = false
[COLOR=green]'Do your thang[/color]
application.enableevents = true
End Sub

Enter into a workSHEET module & this will run whenever a change is made to your sheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MISMonkey]

Thanks Geoff,

It's the 'do your thang' bit I'm struggling with.

As I said - I can't work out how to 'detect' what row the user has altered in column A and then use this information to update columns in that very same row.

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[MISMonkey]

Thanks Geoff,

It's the 'do your thang' bit I'm struggling with.

As I said, I need to 'detect' what row in column A the user has changed and use this information to then update the adjacent columns of the same row.

Any help is always appreciated though.

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[mikej336]

Just check your value and loop thru it.

Dim RowIndex As Integer
RowIndex = 1

Dim ACell As Range
Set ACell = Range("A" & Trim(Str(RowIndex)))

Do While Not IsEmpty(ACell)
SType = ACell.Value
If stype="default" Then
Sheet1.Range("B" & Trim(Str(RowIndex))).Value = 1
Sheet1.Range("c" & Trim(Str(RowIndex))).Value = 2
Sheet1.Range("d" & Trim(Str(RowIndex))).Value = 3
Sheet1.Range("e" & Trim(Str(RowIndex))).Value = 4
end if
RowIndex = RowIndex + 1
Set ACell = Range("A" & Trim(Str(RowIndex)))
Loop

You could even create a formula like

Sheet1.Range("f" & Trim(Str(RowIndex))).Formula = "=e" & Trim(Str(RowIndex)) & "* D" & Trim(Str(RowIndex))

Good luck

Uncle Mike

 

[xlbo]

I would've thought that the line Target.Column would've given you the clue that you could use Target.ROW. However, there is no need to do even that. If you read the help file for the Change event, you will notice that TARGET is a range. you may simply use the OFFSET method to work with other cells

mikej336 - your method would require a loop through ALL entries every time any entry is changed. There is no need for this as you can use

Private Sub Worksheet_Change(ByVal Target As Range)

'test to see if the change occured in Col A
if target.Column <> 1 then exit sub

application.enableevents = false

Target.offset(0,1).value = "Value in Col B"
Target.offset(0,2).value = "Value in Col C"

application.enableevents = true
End Sub

The enableevents lines prevent the change event from continually being triggered by the changes you are making in the code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MISMonkey]

All - Here is what I have at the moment. This seems to work BUT if you highlight multiple cells in column A it crashes...

Can anyone refine this?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False

If Target.Column = 1 Then
ThisRow = Target.Row
If Target.Value = "Default" Or Target.Value = "default" Then
Range("B" & ThisRow) = "1"
Range("C" & ThisRow) = "2"
Range("D" & ThisRow) = "3"
Range("E" & ThisRow) = "4"
Else
Range("B" & ThisRow) = ""
Range("C" & ThisRow) = ""
Range("D" & ThisRow) = ""
Range("E" & ThisRow) = ""
End If
End If

Application.EnableEvents = True
End Sub

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[PHV]

If Target.Column = 1 [!]And Target.Rows.Count = 1[/!] Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MISMonkey]

All - thanks for your help.

Here is what I now have;

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 1 Then Exit Sub
If Target.Value = "Default" Or Target.Value = "default" Then

Application.EnableEvents = False

Target.Offset(0, 1).Value = "1"
Target.Offset(0, 2).Value = "2"
Target.Offset(0, 3).Value = "3"
Target.Offset(0, 4).Value = "4"

Application.EnableEvents = True

End If

End Sub

...still gives an error if you highlight and delete multiple cells in column A. this isn't a problem in my case though.

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[SkipVought]

What about d[red]E[/red]fault???

maybe...

...
If [b]LCase[/b](Target.Value) = "default" Then
...

Skip,
[sub]
[red][/red]
[/sub]

 

[MISMonkey]

Good call Skip.

For my example though I have decided to restrict the user input in column A using a validation list. The users can now only select 'Default' from the list.

I have also changed the 'hard' coded values to be written in the coresponding row so that they reference users values on the sheet (allows a little more flexability from user to user). For any one interested here is what I currently have (still with a bug if you delete multiple cells in Column A though)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 4 Then Exit Sub
If Target.Value = "Default" Then

Application.EnableEvents = False
'picks up the user values in e4 to h4 and
'copies to the target row

Target.Offset(0, 1).Value = Range("e4")
Target.Offset(0, 2).Value = Range("f4")
Target.Offset(0, 3).Value = Range("g4")
Target.Offset(0, 4).Value = Range("h4")

Application.EnableEvents = True
Else
Application.EnableEvents = False

'if the user de-selects the default the cells are cleared
Target.Offset(0, 1).Value = ""
Target.Offset(0, 2).Value = ""
Target.Offset(0, 3).Value = ""
Target.Offset(0, 4).Value = ""

Application.EnableEvents = True

End If

End Sub


I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)