Cell change on User Click

  • Thread starter Thread starter hibatt
  • Start date Start date
H

hibatt

Is it possible to have a cell change to a default value when a use
clicks on it?

I have an old sheet I am working on that people put in manual "X" in t
select options. Most of the sheet uses these X's to calulate option
and such and I would rather not rewrite the entire spread.

Any help would be appreciated.

Dre
 
hibatt said:
*Is it possible to have a cell change to a default value when a use
clicks on it?

I have an old sheet I am working on that people put in manual "X" i
to select options. Most of the sheet uses these X's to calulat
options and such and I would rather not rewrite the entire spread.

Any help would be appreciated.

Drew *
Click to expand...

You could try selecting the "Data" menu then "Validation". There is
drop down box with various options. One of these is "List" whic
allows you to put in only the data you want. In your case this migh
be "X" and " ". Each cell that you validate in this way will now hav
a drop down arrow that allows the user to select "X" or " ".

Not quite what you asked for but it would prevent the users fro
entering something unxepected
 
Drew,

Here is an example

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not interesct(Target, Range("A1:A100")) Is Nothing Then
Target.Value = "X"
End If

End Sub

As worksheet event code, it goes in the worksheet code mosule.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Drew

AFAIK you can not react on a single click with the left mouse button.
Two possible workaround:
1. React on a doubleclick by processing the worksheet_beforedoubleclick
event:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
Cancel = True
Target.Value = "X"
End Sub

This will put 'X' into a cell in column A

2. Use the right mouse button as event:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
Cancel = True
Target.Value = "X"
End Sub


But maybe a better solution would be to use data validation on these
cells and provide a listbox with allowed values

HTH
Frank
 
HI,

How can I have fill color of cell change according to due date. In
example, red overdue, green - on time, black complete.

Thanks, K
 
Hi Klonja

maybe something is wrong with your newsreader. Besides your correct
post as a new thread, this topic has been posted at least to two
already existing threads (both have nothing to do with your problem)
Frank
 
Frank,

Sorry for butting in like this. Can you direct me in the right
direction, or do you know solution for my problem?

Thanks, Klonja
 
Back
Top