Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding values to cells
The following macro lets you take a list in excel: data validation and
lets you store the values you add into the cell. The only draw back is that you cant delete the cell information from the top where the forumlar bar is. Whenever you do it repeats it self. Anyone want to figure out how to change it around so you can delete the values at the top of the formular bar? If you look at the code you'll notice that it works when you have data validation in columns 3 - 4-5. just add an or command for more colmns. Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String If Target.Count 1 Then GoTo exitHandler On Error Resume Next Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) On Error GoTo exitHandler If rngDV Is Nothing Then GoTo exitHandler If Intersect(Target, rngDV) Is Nothing Then 'do nothing Else Application.EnableEvents = False newVal = Target.Value Application.Undo oldVal = Target.Value Target.Value = newVal If Target.Column = 5 Or Target.Column = 3 Or Target.Column = 4 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically adding values to cells... | Excel Discussion (Misc queries) | |||
adding values of cells | New Users to Excel | |||
Adding Values to Cells | Excel Discussion (Misc queries) | |||
adding values in coloured cells | Excel Discussion (Misc queries) | |||
Automatically adding cells with values | Excel Discussion (Misc queries) |