Conditional Cell Change Value

Jas · Aug 25th 2003

Cell A1 can have any value.

Cell B1 has data validation in place, and thus can only have four values.

If I type "run" in cell A1, I want the value in cell B1 to change from "Slow" to "Fast."

The kicker is this: Cell B1 cannot be a formula like =IF(A1="run","fast","slow"). It needs to be the text string "fast" or "slow" or any of the other values in its data validation table.

I've seen a spreadsheet that populated values based on another cell's entry without a formula, but the spreadsheet has a password on it and I cannot figure out how it was done (see attachment).

I'm curious if this can be done with a macro... I've successfully written a macro that will change the value in cell B1, but is there a way to cause a value to run only if the value in cell A1 is changed to a specific text value?

Thanks much!

Anonymous · Aug 25th 2003

Your attachment does not seem to match your problem description. But from your description, it sounds like you might be better off using the CHOOSE() function than data validation. Suggest you take a look at it in Excel HELP.

rem1224 · Aug 26th 2003

Hi Jas -

The worksheet change event macro may be what you need. Without knowing more about you applicaiton, its hard to expand much, but the attached shows the macro in its basic form - a change to A1 causes a change to B1. - - Its not in a "module", but in the code for sheet1 (right click on the sheet1 tab, then click on view code to see it)

Hope this gets you started

.....Ralph

Jas · Aug 26th 2003

That's beautiful! Thanks much - it's exactly what I was looking for. I've not yet implemented it on my worksheet (it's at work and I'm at home), but this looks perfect.

And my local "gurus" said it couldn't be done! :spin:

Paddyd · Aug 26th 2003

FWIW, you can achieve a similar result using data validation. search the site for 'dynamic lists' or something similar - as I recal, Dave has an example workbook describing a technique...

Jas · Aug 26th 2003

I have a new question for you. Is there any way, in your example, to add to the macro that changing cell A1 to a value OTHER than "run" will cause another action to take place (for example, return a default text value or clear the cell). I'm not sure how to trigger that... perhaps an "else" type line?

I'm obviously not the best VBA guy, but I'm learning! :wink2:

Jas · Aug 26th 2003

Nevermind... got it... Here's what I'm using:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myrng As Range
Set myrng = Range("s22")

If Intersect(Target, myrng) Is Nothing Then Exit Sub

If Target.Cells.Value = "Presented Galleria" Then

Range("O27").Value = " "

End If

If Target.Cells.Value <> "Presented Galleria" Then

Range("O27").Value = "Additional Feature"

End If

End Sub

Richie(UK) · Aug 26th 2003

Hi Jas,

You can add a little more scope, and remove tedious multiple Ifs, by using a Select Case statement.

Modifying the example code slightly:

Code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim myrng As Range, mystr As String
    
    Set myrng = Range("a1")
    If Intersect(Target, myrng) Is Nothing Then Exit Sub
    
    Select Case Target.Value
        Case Is = "run": mystr = "Fast"
        Case Is = "walk": mystr = "Slow"
        '
        'etc etc
        '
        Case Else: mystr = vbNullString
    End Select
    Cells(1, 2).Value = mystr
    
End Sub

Display More

HTH

Conditional Cell Change Value

Participate now!

Share

Users Viewing This Thread