Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation lists... | Excel Discussion (Misc queries) | |||
Drop down lists and macros | Excel Worksheet Functions | |||
How to clear validation lists based on other validation lists | Excel Discussion (Misc queries) | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
pasting into lists using macros | Excel Discussion (Misc queries) |