But what I am looking for is the value of the cell should change as soon as the user enters its value . Click on the one showing change to view all of the events for a worksheet. But is there a way to run the macro from the list of custom formats??
So when user enters 1 its hould change to Read-only . Next, go to the workbook section of the VB Editor (on the left hand in the project explorer (Ctrl-R if it isn't there) select the item called This Workbook. I mean I am exactly looking at running a small procedure by adding it to the custom format.
This is what I have: Sub Chg Info() Dim Sht As Worksheet For Each Sht In Worksheets Columns("G: G"). Replace What:="1", Replacement:="ACTIVE USER", Search Order:=xl By Rows, Match Case:=False Sht. Replace What:="1", _ Replacement:="1-Read Only", Search Order:=xl By Columns, Match Case:=False Sht. Replace What:="2", _ Replacement:="2-Assessor", Search Order:=xl By Columns, Match Case:=False Sht. Replace What:="3", _ Replacement:="3-REVIEWER", Search Order:=xl By Columns, Match Case:=False Next End Sub Also, I wanted to know if there is a way to add our own format in the custom format field in 'Format Cell' menu and pick custom??? Do the same thing to the drop-downs on top to view the events for a workbook. I understand that using cutsom format we can change the entire column in one format but what if I wanted to have 3 different values in the same column?? As far as I know (and as much as I understand you), you cannot run code with a custom format.
Thanks in advance Private Sub Worksheet_Change(By Val Target As Range) Debug. You can however create a macro to do the work and add it to a tool bar button or a menu item.
Till now I have been using this Here "range1" is the range which is to come in the data validation list and "rng" is the cell where I want the data validation Dim range1, rng As range Set range1 = range("a1:a5") Set rng = range("b1") With rng With . Add Type:=xl Validate List, Alert Style:=xl Valid Alert Stop, Operator:= _ xl Between, Formula1:="range1" End With End With Dim ws As Worksheet Dim range1 As Range, rng As Range 'change Sheet1 to suit Set ws = This Workbook.
Add Type:=xl Validate List, Alert Style:=xl Valid Alert Stop, _ Formula1:="='" & ws.
The original sheet that the data is copied from is called "Data Entry", the sheet that is created does not have a static name and instead it takes its name from the client reference number inputted in cell D3 of the Data Entry sheet.
I want to add "data validation" in a cell(which is variable) using VBA and the range which is to come in the data validation list is also variable.
The best thing about using a table is that as you add or remove items from your list, your data validation list will update automatically.
Note: The following examples use the Custom option where you write formulas to set your conditions.
Depending on what you are trying to do, you may need to treat these two types of blank cell differently. If A1/B1 returns an error then a blank cell is displayed.
The second is a cell that has a formula that returns a blank cell (see examples below).