Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deselect cells with formulas in selection
Hi all,
i have one problem to solve and can't get it done. (I am new to VBA) I have a table with calls that the user can fill and some with formulas. If the user makes a selection mith his mouse (e.g. from A1 to F20), he also selects cells with formulas. What i want to do, ist to deselct these cells by a script/vba program, after the user made his selection, so that only the non-formula cells stay selected. How can i do this? I think it has to be this way, but i don't know how to deselect a cell. 'Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim c As Range For Each c In Target If c.HasFormula Then ??? Next End Sub I this the right way? What is the missing comand? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deselect cells with formulas in selection
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range, d as Range On Error goto ErrHandler For Each c In Target If Not c.HasFormula Then if d is nothing then set d = c else set d = union(d,c) end if Next Application.EnableEvents = False if not d is nothing then d.Select else Range("A1").Select end if ErrHandler: Application.EnableEvents = True End Sub If there are no cells that are blank - either constants or formulas, you could use specialcells Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim c As Range, d as Range On Error goto ErrHandler Application.EnableEvents = False d = target.specialcells(xlConstants) if not d is nothing then d.Select else Range("A1").Select end if ErrHandler: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Thorsten Gleumes" wrote in message ... Hi all, i have one problem to solve and can't get it done. (I am new to VBA) I have a table with calls that the user can fill and some with formulas. If the user makes a selection mith his mouse (e.g. from A1 to F20), he also selects cells with formulas. What i want to do, ist to deselct these cells by a script/vba program, after the user made his selection, so that only the non-formula cells stay selected. How can i do this? I think it has to be this way, but i don't know how to deselect a cell. 'Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim c As Range For Each c In Target If c.HasFormula Then ??? Next End Sub I this the right way? What is the missing comand? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deselect cells with formulas in selection
"Tom Ogilvy" schrieb:
Many thanks, i will try you solution. Thorsten |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deselect (Unselect) Cells | Excel Discussion (Misc queries) | |||
deselect row/column from multiple selection | Excel Discussion (Misc queries) | |||
programmatically deselect a selection? | Excel Programming | |||
Deselect Cells while using ctrl | Excel Discussion (Misc queries) | |||
Deselect of Cells | Excel Programming |