To increase mouse clicked Cell value = previously mouse clicked cell value + 1

[upan275]

To ALL Macro-Men,

Please allow myself to welcome in a New Regime of VBA!
Please help!!

My ultimate Goal: To increase mouse clicked Cell value = previously mouseclicked cell value + 1 Range (A4:F57)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Initially Cellvalue will be first data-entered cell by user(i.e. “A4” need user input box for this) (Perhaps I cannot eliminate this step as user should enter first value, say A4= "5000")<o></o>

Previously clicked cell canbe any random cell in the same Range (“A4:F57") <o></o>

So far I have code:<o></o>

At present I am using DoubleClickEvent to copy “A4” value first & then increase value by right click event…(Not a real solution!)<o></o>

Any other Best Solution ???

[SIZE=3][FONT=Times New Roman]Private SubWorksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] Target.Value = Range("A4").Value<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]If Target.Cells.Count >1 Then Exit Sub<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]If Not Intersect(Target,Range("myrange")) Is Nothing Then<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] Cancel = True <o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]Target = Target + 1<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] End If<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]End Sub<o:p></o:p>[/FONT][/SIZE]
------------------------------------------------------------------------------------ 
[SIZE=3][FONT=Times New Roman]Private SubWorksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  If Target.Cells.Count > 1 Then Exit Sub<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  If NotIntersect(Target, Range("myrange")) Is Nothing Then<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  Cancel = True<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  Target = Target + 1<o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] End If   <o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]End Sub<o:p></o:p>[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][/CO[/SIZE][/FONT]DE]

 

[Weaver]

try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error GoTo safeExit
    If Intersect(Target, Range("A4:F57")) Then
        If Not IsEmpty(Target) Then
            Application.EnableEvents = False
            Target.Value = Target.Value + 1
            Cancel = True
        End If
    End If
safeExit:
    Application.EnableEvents = True
End Sub

If the cell is empty, you'll be allowed to click into it as normal, so you can type in a number. If the value in the cell is non numeric, then nothing happens. I don't think you need to worry about the active range being > 1 cell as you don't seem to be able to double click multi cells, although I've not tested this exhaustively.

 

[upan275]

Hi Weaver,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Thanks foryour Prompt Reply,
But it’snot what I am looking for…Your Code gives same result as my code inPrivateSubWorksheet_BeforeRightClick event<o></o>

Let me explainthe Problem again:<o></o>

I want toenter Ascending series number in Range (“A4: F57” = myrange ) ..Say start with3000, 3001….3002….3004…3006…. & so on. As I don’t want to type it all, Ienter ONLY first number of Series (Say A4=3000) & after this I use doubleclick event to enter Next number (in series) randomly in same range.<o></o>

Hence when Idouble-click any random cell in Range (A4:F57) after manually entering firstnumber (Say A4=3000), then VBA would remember Last clicked and/or Last userentered cell value & increase by +1and insert as Target value.<o></o>

For e.g.<o></o>

If A4 =3000 THEN Double clicked-randomly selected cell F50 results 3001<o></o>

If F50 =3001 THEN Double clicked-randomly selected cell C15 results 3002 <o></o>

If C4 = 3002THEN Double clicked-randomly selected cell B5 results 3003…Also if I doubleclick B5 TWICE it should results 3004..<o></o>
Now let’ssay I change “B5” Manually as 6004 Then next cell double clicking should Results6005..<o></o>

In short, currentlydouble clicked cell value = previously double-clicked cell value OR Previouslyuser entered cell value +1<o></o>

For this Ineed Excel to remember lastly selected cell value (which can be any cell in Range,Cell Locating with Offset doesn’t help in this case)<o></o>

BTW, I believethere must be some code in Excel to remember most recent 2-3 changes occurredin sheet. Otherwise Undo (Cntl+Z) can’t be possible.I tried toRecord Macro with Undo event but Macro doesn’t record undo code…<o></o>

This time I hope I explain it properly<o></o>

<o></o>
Well said:
“We can't solve problems by using the same kind of thinking we used when we createdthem.”-Albert Einstein<o></o>


<o><o></o></o>

 

[Weaver]

To be fair, that's hardly "Explaining the problem again", in fact I'd call it "explaining the problem"

If you want to 'remember' previous values, you can 'dim' variables outside your event handler. These variables will then maintain their values through different iterations of the handler. So if the handler changes such a value and is then called again, the variable will contain whatever you put in it last time. This is how you can 'remember' what was clicked, selected or updated from the previous activation of the handler.

HTH

 

[upan275]

???

May I ask you to explain with some Example code?

Is it what you want to explain ? http://www.ozgrid.com/forum/showthread.php?t=51722
Cannot customised this as per my requirement...

 

[Weaver]

I don't think this is exactly what you're after, but hopefully it will demonstrate the principles.

Clear all the code out of your worksheet module and replace it with this:

Dim d As Double 'note this is outside of all the subroutines
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error GoTo safeExit
    Debug.Print d
    If Not Intersect(Target, Range("A4:F57")) Is Nothing Then
        Target.Value = d
        Cancel = True
    End If
safeExit:
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsEmpty(Target) Then
        d = Target.Value + 1
        Debug.Print Target.Address, d
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not IsEmpty(Target) Then
        d = Target.Value + 1
    End If
End Sub

Now, if you click or select a cell with a number in it, that number is recorded in d, which doesn't lose its value once the subroutine ends. When you click on an empty cell the value of the cell, plus 1 should appear in the empty cell. If you select an empty cell and type a number in it, that value gets recorded and updated also.

Note, it could probably do with a bit more error trapping.

 

[upan275]

BRILLAINTLY WEAVED CODE WEAVER!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

When first time I select more than one cell (just to delete &re-enter), I got error “13” , was easy fixed with: On Error GoTosafeExit<o></o>

Also added Rightclick event to reduce number.<o></o>

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
 If Not Intersect(Target, Range("A4:F57")) Is Nothing Then
        Target.Value = Target.Value - 1
        Cancel = True
End Sub

<o></o>
Thanks a bunch!!


<o></o>

<o></o>

 

[Weaver]

No problem, glad for the feedback, and that you were able to modify & adapt it for your needs.

Happy Christmas

 

[Weaver]

PS you probably alrready figured this out, but you can take out the 'debug.print' statements - these were just for my benefit while I was coding.