Select active cell

nogoodnames · #1 (**permalink**) 03-08-10, 10:56

Hi all, new member here. Been using the forum for answers for awhile, but have just registered as I now need to ask a question of my own.

First of all, I'm relatively new to using macros, and my programming knowledge is... let's just say limited.

I am trying to find a way to stop me from having to create/assign 336 different macros... for each person that will be using the spreadsheet (about 50 people). Surely there's an easier way.

What I have is a cell with a user input value. They have full access to edit the value, etc. I'm trying to put in a button on either side of the value to increase / decrease the value by 1. A simple + and - button, rather than have to click the cell, delete the current value and input the new number.

What I've got so far is:

Sub Plus1_B6()
Range("B6") = Range("B6") + 1
End Sub

Sub Minus1_B6()
Range("B6") = Range("B6") - 1
End Sub

Which both work, but mean I have to create/assign a new macro for every cell needed. Which is 28 cells per day, 6 days a week. 56 each day counting the + and -.... which I will then have to recreate for roughly 50 people.

Is there someway to have the macro select the cell in which the button is hit, so that the same macro would suffice for every button? What I've got is just the two small buttons on either side of (but still inside) the cells, with the number in the middle.

I hope that makes sense... and any and all help is greatly appreciated! Thanks in advance!

Colin Legg · #2 (**permalink**) 03-08-10, 17:12

Welcome to the forum.

Quote:

What I have is a cell with a user input value. They have full access to edit the value, etc. I'm trying to put in a button on either side of the value to increase / decrease the value by 1. A simple + and - button, rather than have to click the cell, delete the current value and input the new number.

What I've got so far is:

Sub Plus1_B6()
Range("B6") = Range("B6") + 1
End Sub

Sub Minus1_B6()
Range("B6") = Range("B6") - 1
End Sub

Which both work, but mean I have to create/assign a new macro for every cell needed. Which is 28 cells per day, 6 days a week. 56 each day counting the + and -.... which I will then have to recreate for roughly 50 people.

Yes, I think we can try to find an easier way of doing this.

First let's stick with using a controls approach. Rather than using two command buttons, you could use a spin button. If you use a forms spin button control, when you add it to the sheet you can right click | format control, go to the control tab and link it to a cell. You can also specify incremental change, min, max etc... So, using this control, you get the effect you want without the need for VBA. I'm not sure what distinction is required between different people?

An alternative to using controls might be to utilise a worksheet event handler. The advantage of this would be that it would remove the clutter of controls from the worksheet. The main disadvantage is that it would only work if the end users enable macros. It's just a question of choosing the event handler that suits you best. For example, perhaps the end users don't need the cell menu from right clicking on the cells so you could make it that right clicking on the cells increases their value by one? You would use the worksheet's BeforeRightClick() event handler for this:

Code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    'only do this for cells A1:A100
    If Not Intersect(Range("A1:A100"), Target.Cells(1)) Is Nothing Then
        Target.Cells(1).Value = Target.Cells(1).Value + 1
        Cancel = True
    End If
End Sub

Other alternatives might be double clicking or simply selecting the cell?

Hope that helps...

nogoodnames · #3 (**permalink**) 03-09-10, 03:22

That helps immensely! I had no idea what a spin button was, but a quick google search and I had my answer. That's exactly what I was looking for, and you're right, no need for a bunch of extra code.

However, I still need to link it to each cell, though the workload is halved to 84, it's still a fair bit for eachperson. Is there a way to link the spin button to the cell it's in automatically?

Thank you very much for the help!

Colin Legg · #4 (**permalink**) 03-09-10, 04:32

Quote:

However, I still need to link it to each cell, though the workload is halved to 84,

More than that: carefully positioning the spin buttons on the worksheet is going to take some time! Yes, you could write some VBA code to create, position and link the spin buttons to the worksheet. Here's an example:

Code:

Sub foo()

    Const lngWIDTH As Long = 10
    Const lngMAX As Long = 10000
    Const lngMIN As Long = 0
    
    'Spinners are hidden members so we'll use variables to get the intellisense benefit
    Dim objSpinner As Spinner, objSpinners As Spinners
    
    Dim rngCell As Range
    
    Set objSpinners = Spinners
    
    'add spinner buttons to the cells in range A2:A15
    For Each rngCell In Range("A2:A15").Cells
        Set objSpinner = objSpinners.Add( _
                                Left:=rngCell.Offset(, 1).Left, _
                                Top:=rngCell.Top, _
                                Width:=lngWIDTH, _
                                Height:=rngCell.Height)
        
        objSpinner.LinkedCell = rngCell.Address(external:=True)
        objSpinner.Placement = xlMoveAndSize
        objSpinner.Min = lngMIN
        objSpinner.Max = lngMAX
        
    Next rngCell
    
End Sub

But are you sure you don't want to go for the right-click (or other event handler) option?

nogoodnames · #5 (**permalink**) 03-09-10, 04:38

Am I sure I don't want to? No, I'm not sure. lol. I quite like the right click idea to be honest, but as there's going to be so many people using it, I think it may be easier to have a visual button for people to click. However, I'll look at the right clicking option as well. I've got a bad case of tunnel vision sometimes.

For the original code you posted... how do I actually get it to call on a right click? You mentioned a worksheet event handler... but I don't know what that is.

I've added the code... just not sure how to call it. When I protect the sheet and right click, I still get the normal menu.

As for the new code you just wrote... it looks like it's perfect for me (with my tunnel vision! haha!), and I'm just away to try it now. I'll let ya know.

And again, thank you very much. You are truly helping a great deal!

edit:
Tested your code, and I'm getting a runtime error at Set objSpinners = Spinners
Says: object required

I'm not sure if I was supposed to add something to the code or not?

nogoodnames · #6 (**permalink**) 03-09-10, 06:05

Update:

Ok, I have the event handler working. Google is my friend!

It's definitely cleaner looking than buttons, no doubt about that. The only problem I have is that if they right click on a locked cell (which are currently set to be un-selectable), the previous active cell still increases by 1. When I allow selecting of locked cells, I get a runtime error as it's trying to modify a locked cell. I doubt people will do it on purpose... but it could happen inadvertently, which would then throw the numbers out of whack... especially with the event handler being 'invisible'. Any easy solution to that?

As for the code you wrote earlier... I haven't been able to get that working unfortunately.

Colin Legg · #7 (**permalink**) 03-09-10, 06:08

Quote:

I'm not sure if I was supposed to add something to the code or not?

If you pasted it into a standard code module, amend this line to include activesheet:

Code:

Set objSpinners = ActiveSheet.Spinners

Or, alternatively, paste it into the sheet's class module instead of using a standard code module.

Colin Legg · #8 (**permalink**) 03-09-10, 06:37

Quote:

Originally Posted by nogoodnames

Update:

Ok, I have the event handler working. Google is my friend!

It's definitely cleaner looking than buttons, no doubt about that. The only problem I have is that if they right click on a locked cell (which are currently set to be un-selectable), the previous active cell still increases by 1.... Any easy solution to that?

Yes, protect the sheet allowing selection of locked cells. Then add a check into the event handler to see if Target is locked. eg

Code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    'only do this for cells A1:A100
    
    If Not Intersect(Range("A1:A100"), Target.Cells(1)) Is Nothing Then
        If Not Target.Cells(1).Locked Then
            Target.Cells(1).Value = Target.Cells(1).Value + 1
            Cancel = True
        End If
    End If
End Sub

Or use an alternative event handler.

Quote:

As for the code you wrote earlier... I haven't been able to get that working unfortunately.

See my previous reply #7. If you are still having trouble with it I will attach an exampe for you.

Hope that helps...

nogoodnames · #9 (**permalink**) 03-09-10, 07:12

First of all, thank you VERY much for all the help so far.

Ok, I've got the event handler working with the locked cells. Perfect.

As for the other code, I've been able to get it to run when I added it to the class module and a standard module (editing the line you indicated). That being said, it's worked like a charm now!

The only thing I need is to move the spinner into the cell. Currently it's out of the cell on the right hand side of it... I just want to move it from outside the cell into the cell.

Thank you so much!

Colin Legg · #10 (**permalink**) 03-09-10, 07:24

Quote:

The only thing I need is to move the spinner into the cell. Currently it's out of the cell on the right hand side of it... I just want to move it from outside the cell into the cell.

It the width of the "counter" cells column is going to change then its better to put them in the cell next door. If you want them in the right side of the "counter" cells then it's just a case of amending the left argument to this:

Code:

Sub foo()

    Const lngWIDTH As Long = 10
    Const lngMAX As Long = 10000
    Const lngMIN As Long = 0
    
    'Spinners are hidden members so we'll use variables to get the intellisense benefit
    Dim objSpinner As Spinner, objSpinners As Spinners
    
    Dim rngCell As Range
    
    Set objSpinners = ActiveSheet.Spinners
    
    'add spinner buttons to the cells in range A2:A15
    For Each rngCell In Range("A2:A15").Cells
        Set objSpinner = objSpinners.Add( _
                                Left:=rngCell.Offset(, 1).Left - lngWIDTH, _
                                Top:=rngCell.Top, _
                                Width:=lngWIDTH, _
                                Height:=rngCell.Height)
        
        objSpinner.LinkedCell = rngCell.Address(external:=True)
        objSpinner.Placement = xlMoveAndSize
        objSpinner.Min = lngMIN
        objSpinner.Max = lngMAX
        
    Next rngCell
    
End Sub

Quote:

Thank you so much!

You're welcome. Good luck with the rest of your project.

nogoodnames · #11 (**permalink**) 03-09-10, 07:29

Works a treat.

Thanks again, you've been incredible!

Internet Services

Web Development

Digital Marketing

Consumer Tech