Fill cells

A

art

Hello:

Is there a way that I can make through anyway (macro, VBA) that when I
select a cell, it should automatically fill in the number 1. and If I select
the cell twice the cell should fill in the number 2. Any help would be
appriciated.
 
K

Kent Prokopy

I do not think this can be done as there is no Cell_Click method that I can
find.
 
D

Dan R.

Not sure if this is what you need or not but right-click the tab of
the sheet, hit View Code and paste this in there:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Target = Target.Value + 1
End Sub
 
A

art

Thanks. How about this. Is it possible to make that when I click on a cell, a
form control box that has a macro should float near the cell? i want to make
the button that has a macro float to where the mouse is. Is it possible?
 
A

art

Thanks. But is there a way, to have the option to enter more then one. Or at
least that if I wnat to enter more then 1, then nothing should fill in? How
about a popup window?
 
R

Rick Rothstein

This may not be a satisfactory solution because in order to select the cell
a second time you have move the selection away from the cell and then back
to it again. Anyway, here is the event procedure which will do this..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
On Error GoTo FixMeUp
Application.EnableEvents = False
Target.Value = Target.Value + 1
End If
FixMeUp:
Application.EnableEvents = True
End Sub

To install it, right click the page where you want this functionality,
select View Code from the popup menu that appears, and then copy/paste the
above code into the code window that appeared. Now, since you didn't say
which cell you wanted to have this functionality, I guessed at A1; if that
is wrong, just change the A1 in the If..Then statement to the cell address
of the cell you want for your incrementer. By the way, if you could live
with a double click to activate the incrementer, then use this code
instead...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Address(False, False) = "A1" Then
On Error GoTo FixMeUp
Cancel = True
Application.EnableEvents = False
Target.Value = Target.Value + 1
End If
FixMeUp:
Application.EnableEvents = True
End Sub

With this code, you don't have to move focus away from the cell and back
again... just keep double clicking and the value will advance.
 
A

art

I have the following VBA. BUt I would like to know how to make that when
right clicking it should not go less then zero. If I right click on a 1 then
it should turn empty.
 
A

art

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub

Cancel = True 'stop editing in cell
If IsNumeric(Target.Value) Then
Target.Value = Target.Value + 1
End If
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub

Cancel = True 'stop pop up from showing
If IsNumeric(Target.Value) Then
Target.Value = Target.Value - 1
End If
End Sub
 
Top