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

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

Deselect cells with formulas in selection
 

"Tom Ogilvy" schrieb:


Many thanks, i will try you solution.

Thorsten