Change cell value to "1" by mouseclick

[joh123]

Hi,
I haven't touched Excel in a while and it seems I'm totally unable to get anything working anymore

I need to make cells E5 to E39 act so that if I click on one, the empty cell gets value "1". It would be even better if the second click would make it "2", third "3" and so on, but if that's too complicated, just one click to "1" will do.. can anyone help me out? I can't even get my macros to show up..

The file is here: http://www14.zippyshare.com/v/FL8O0lnu/file.html
Although, in theory the file is not even needed

I'd really appreciate help.

Thank you guys in advance!

[JBeaucaire]

Since more than one cell at a time could be clicked, this will evaluate all currently selected cells, this goes in the SHEET module.

Please Login or Register  to view this content.

[joh123]

Woah Thank You! [rep added]
This will pretty much do! Although, do You have any idea if it's possible to make it so that if I do two or three clicks on one cell, it will increase. Because at the moment, I have to click on another cell and then again on the first cell for it to increase. Two or more clicks won't work if I don't click on another cell in between..

[JBeaucaire]

We can fake it by moving the selected cell off that clicked cell(s).

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[joh123]

You, Sir, are brilliant!
I also replaced

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

so I can also delete the value if needed (otherwise it'd be impossible) and also otherwise it will leave a trail of ones if I move around with keyboard arrows.

But all in all, it's perfect now and I sincerely thank You for Your time!
Greetings!

[JBeaucaire]

Smart change. Good job.

[joh123]

Hi!
Now I've encountered another and rather unexpected problem. I was planning to use the worksheet on a Windows 10 tablet with touchscreen, yet Excel doesn't seem to register the touches the same as clicks - so the value will not change and all that happens is the cell going active.

What do you guys think, is there a workaround?
Greets!

[JBeaucaire]

Nope. Sorry.

https://answers.microsoft.com/en-us/...f-b1c49da34c73

There are a few Office features that are not available in Office 2013 for Windows RT:
http://office.microsoft.com/en-us/ho...103210361.aspx
Like macros and forms.
These features are available in the Windows 8 version of Office 2013. You can use Office 2013 on Surface with Windows 8 Pro.
Hope this helps,
Kate

[BellyGas]

Hi!
Now I've encountered another and rather unexpected problem. I was planning to use the worksheet on a Windows 10 tablet with touchscreen, yet Excel doesn't seem to register the touches the same as clicks - so the value will not change and all that happens is the cell going active.

What tablet? My MS-Surface Pro 3 recognises touches as clicks....

[joh123]

Tried on iPad 2 (with MS Excel) and a friend tried on his (don't know which one). It works if I connect a mouse to the tablet - so VBA itself works, but with this solution posted above it doesn't when I "click" with my finger

[BellyGas]

There's your problem. iPad. I could go off on my usual rant about how Apple do everything half arsed AND produce their hardware in sweatshops in asia with child labour AND still manage to charge more for their kit than could possibly be justified but......I won't. Damn, too late.

Buy a proper windows tablet and it'll work.

[joh123]

There's your problem. iPad. I could go off on my usual rant about how Apple do everything half arsed AND produce their hardware in sweatshops in asia with child labour AND still manage to charge more for their kit than could possibly be justified but......I won't. Damn, too late.

Buy a proper windows tablet and it'll work.

Yeah, I don't like them either. But still, I tried it on an iPad and also on a Win10 tablet and it was the same Gotta try it on another Win10 tablet then before reaching any conclusions, I guess. Thanks for the input.

[BellyGas]

Or try a win8 tablet.

[joh123]

Okay, I tried it on a Win8 Lenovo Thinkpad Yoga-something tablet and it won't work. However, it seems that the touch activates it totally differently. See these pictures:

A regular click activates a cell like this:
1mouseclick.png

and double-click does what it should - increases the value of the cell by 1

Now, a touch activates a cell like this:
1touch.png

And a double-touch like this:
2touch.png
and now I can write in the cell..

Maybe these pictures help to resolve the problem..

Do any of you have any idea?

[JBeaucaire]

No, my understanding from the link I noted previously is that this isn't going to work.

[xlnitwit]

Tried on iPad 2 (with MS Excel) and a friend tried on his (don't know which one). It works if I connect a mouse to the tablet - so VBA itself works, but with this solution posted above it doesn't when I "click" with my finger

Pardon the interruption- are you saying that VBA is working in Excel on an iPad?

[joh123]

Well, how come it works on BellyGas' tablet then.. He said previously that "My MS-Surface Pro 3 recognises touches as clicks...."..

[BellyGas]

It definitely does. I'm using it right now.

[joh123]

It definitely does. I'm using it right now.

Hi, BellyGas

Are we sure we're talking about the same thing?

In this file - http://www99.zippyshare.com/v/FqOW9pNl/file.html - in E-column, does double-touching on cells under "Määrä" increase cell value by 1?

Thanks in advance if you're willing to try the actual file..

[BellyGas]

Nope....but not cos it's a touchscreen:

You've put your macro in the worksheet before double click event, it should be in the worksheet selection change event. Forget double clicking. Even double clicking with a mouse won't trigger it.

You're using too many variables. Forget 'cell' just use 'target', it's already been defined. And that rubbish you've got in the actual important bit is not needed. Your macro should be as simple as target.value = target.value + 1. There is no need for anything else inside the range selection part.

[JBeaucaire]

BellyGas, the original macro was a Worksheet_SelectionChange event, so the "cell" (garbage?) came from that, the need to be able to trap and proceed if more than one cell is selected. I agree converting it to a BeforeDoubleClick event means it is no longer needed, just giving you the history on how it got there.

As a doubleclick event it does what they intend with a mouse.

[BellyGas]

Not on my 2 laptops (1 with a touchscreen, 1 without). Double clicking with a mouse on both does nothing.

[BellyGas]

Silly question: Why is it necessary for this to happen on a double click? Why not a single? Post #1 says it would be nice if it went to 2, 3, etc, on subsequent clicks.....so why does this need to be on a double click?

The code becomes very simple if it's a single click.

Please Login or Register  to view this content.

[JBeaucaire]

The OP wanted the ability to select cells normally across the sheet without incrementing, then only getting the +1 effect when they specifically double-click. I would have opted to stay with the original SelectionChange approach from post #2. But that's me. I get the benefits of both.

[xlnitwit]

A double-click would also have the benefit of allowing you to increment the same cell repeatedly.

[JBeaucaire]

Hehe, I can't help but note that changing a 1 to a 5 with doubleclicks can be characterized as "inefficient". I can do that in 2 keystrokes by typing

[BellyGas]

That's what the Range("A1").select command is for. You can click the same cell repeatedly with it incrementing every time.

And yeah, repeatedly double clicking is just going to give the OPs manager the idea he's playing Counterstrike.

[xlnitwit]

That's what the Range("A1").select command is for.

Yes but that would have the minor side effect of not letting you select anything else on the sheet.

[joh123]

Woah, so many posts. Well, to be honest, I rarely need the value to be more than 1 or blank. So no Counter Strike :D But one click (instead of double) would render me unable to delete the value if I happen to change my mind.

It seems you guys are alot smarter than me and I got pretty confused by all your comments now :P But how come this file doesn't work for you, even with a mouse? Works for me. It just wont work on a touchscreen.. and so far I can't understand if its possible at all or not. I mean it works on a touchscreen computer with a physical mouse attached but won't work using the touchscreen..

[BellyGas]

Yes but that would have the minor side effect of not letting you select anything else on the sheet.

Doh, he's right. Move it to within the target not intersect part and it'll be fine.