+/- buttons

[zugfrig]

Gday

ive put my hand up to do stats for my local footy club.

i designed something in think to be easy to use, but cant work out how to make my + or - numbers increase or decrease the number next to iT

screenshot and excel are here http://thepremiershipclub.wixsite.com/hcfc-stats/blank

 

[jproffer]

Wow, plus on the left, minus on the right...that would be really counter-intuitive for me.....BUT none-the-less ....

If they're buttons, you're coding would be something like:

Sub PlusRange1
    Range("A1").Value = Range("A1").Value + 1
End Sub

And similar for minus buttons. And keep in mind, you're going to have a LOT of similar, but different coding to write.

I'm wondering if you could use a KeyDown event on worksheet change (I'm also wondering if there even IS a Keydown Event with Worksheet events....I'll have to check tomorrow at work (no excel at home), but if there is...and if you're interested it would be 2 lines of code to cover the entire worksheet, I think.

If KeyCode = 38 (I think, I'll have to check that too) then Selection.Value = Selection.Value + 1
If KeyCode = 40 Then Selection.Value = Selection.Value - 1

NOW, if you did that, it would fire that code no matter where you were on the sheet...so no more navigating the sheet with the arrow keys, so if you're in that habit maybe we'd better not even explore that route .

At any rate, anything I can do/test and say for dead certain will have to wait until work tomorrow (about 8-10 hours from now)

 

[zugfrig]

thanks so much for your reply and help.. given its only new i can train myself to do it what ever way is easier.

and lol i didn't even consider the left/right thing.. that does actually make sense.

 

[jproffer]

If I was going to go the button route, myself (personal preference), I'd probably put both buttons on one side or the other...plus on top, minus on bottom, each one a half cell tall. Closer/less movement to go from one to the other. But this is your project my friend.

So if the keydown thing is still an option in your mind (don't get your hopes up...I'm not sure it's an option in the worksheet events, like I said), but I'll check it out tomorrow.

 

[zugfrig]

i get what you are saying but it should be ok the way it is. the stats im recording are all going to be increasing. the minus will only be used if i make a mistake and have to lessen a number (so hopefully not that often)

 

[zugfrig]

im new to this kind of stuff

i right clicked and posted the code into the macro under waht was aloready in there (changing the range to the cell i want to change)

it seemed to take it without wrror adn the button depresses but no change occurs - see below

Sub Button1_Click()


End Sub
Sub PlusRange1()
Range("Q5").Value = Range("Q5").Value + 1
End Sub

 

[jproffer]

Take my middle line and put it between the 2 lines you had already...

Sub Button1_Click
Range("Q5").Value = Range("Q5").Value + 1
End Sub

After you get one working, it will just be copy/paste...change "Button1" to "Button2"/3/4/5/6/7...... and change the Range to whatever for that particular button

 

[zugfrig]

ok great

i have the first button increasing by one... but it seams every button increase it by one

when i first made it i made one button and then copied adn poasted it.... is that going to cause an issue?

if i try adding a new buttin it says button 147 so how can i find out the names of allthe buttons i installed now that i cahnged their name to either + or -

or will i have to delete them all and build it one by one

 

[jproffer]

Wellllllllllllllllllllllllllll.....hmmm...I don't think more than one button on a sheet can have the same name. On a userform, you can't.

Ok, I hate to say it, but I really can't imagine why ALL the buttons are making Q5 increase.

Have a look at Forms (not active-X) spin buttons. Last tab is controls and you can link a cell...set the increment, minimum and maximum. This might just be better. I'm sorry I didn't think of it sooner, but it would eliminate all these troubles...and no coding needed

 

[zugfrig]

the word legend comes to mind..... or is it wizard

who cares, you are both.... very easy solution