Macros and validation lists
 

Hi,

I have a data validation list defined in an excel
spreadsheet. I am trying to run a small macro that ensures
that if a particular list definition is active in the
respective cell(say AAA) then other cells (3 in total) are
set to zero ...
I am not at all familiar with macros - just trying to find
my feet. What I have presently is:

Range("D4").Select
If Range("D4").Value = "AAA" Then
ActiveCell.FormulaR1C1 = "0"
Range("E4").Select
ActiveCell.FormulaR1C1 = "0"
Range("G4").Select
ActiveCell.FormulaR1C1 = "0"
End If
End Sub

However it is not recognising the validation list? I
suspect I am using wrong programming statements. Would
apprecitae if someone could get me off the ground ...

Many thanks,

Don-

Macros and validation lists
 

You could try like this:

If activeworkbook.Cells("D4").Value = "AAA" Then
cells("E4").value = "0"
cells("F4").value = "0"
cells("G4").value = "0"
End If
End Su

--
Message posted from http://www.ExcelForum.com

Macros and validation lists
 

Should have tried that first, before posting. Here is how it should hav
been.....

Private Sub adsf()

If ActiveWorkbook.ActiveSheet.Cells(4, 4).Value = "AAA" Then
Cells(4, 5).Value = 0
Cells(4, 6).Value = 0
Cells(4, 7).Value = 0
End If

End Sub

I prefer the R1C1 cell values. The A1 values should work just fine..

--
Message posted from http://www.ExcelForum.com

Macros and validation lists
 

Thankyou Berend,

Is it possible to define this macro without specific cell
references? My issue is there are multiple excel rows
where this check needs to occur? I was hoping I could
specify something like:
'If 'AAA' from data validation list is active then set the
specific cells associated with that same row to zero'

Rgds,

Don-
-----Original Message-----
Should have tried that first, before posting. Here is how
it should have
been.....

Private Sub adsf()

If ActiveWorkbook.ActiveSheet.Cells(4, 4).Value = "AAA"
Then
Cells(4, 5).Value = 0
Cells(4, 6).Value = 0
Cells(4, 7).Value = 0
End If

End Sub

I prefer the R1C1 cell values. The A1 values should work
just fine...


---
Message posted from http://www.ExcelForum.com/

.

Macros and validation lists
 

First, name the cell that contains your validation list. Working wit
cell coordinates gets messy as your database gets larger. Also, if you
cell moves, then the reference changes.

The 'offset' command seems to be what you're looking for. It applies t
a cell with respect to another. The syntax is
Range("WhateverCell").offset(Number of rows, Number of colums)
If the number of rows, or columns, is 1, the next row or column will b
selected. If the varible is -1, the previous row or column i
selected.

In this example, I've named the cell D4, "valid1".
Sub ValidationChange

If Range("Valid1").Value = "AAA" Then 'range D4
With Range("valid1")
.offset(0,1).value = 0 'range E4
.offset(0,2).value = 0 'range F4
.offset(0,3).value = 0 'range G4
End With
End If

End Sub

- Gitcyphe

--
Message posted from http://www.ExcelForum.com