Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2010
    Posts
    27

    Unanswered: Select active cell

    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!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Welcome to the forum.

    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...

  3. #3
    Join Date
    Mar 2010
    Posts
    27
    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!
    Last edited by nogoodnames; 03-09-10 at 04:29.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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?

  5. #5
    Join Date
    Mar 2010
    Posts
    27
    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?
    Last edited by nogoodnames; 03-09-10 at 05:51.

  6. #6
    Join Date
    Mar 2010
    Posts
    27
    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.

  7. #7
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Quote Originally Posted by nogoodnames View Post
    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.
    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...

  9. #9
    Join Date
    Mar 2010
    Posts
    27
    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!

  10. #10
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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
    Thank you so much!
    You're welcome. Good luck with the rest of your project.

  11. #11
    Join Date
    Mar 2010
    Posts
    27
    Works a treat.

    Thanks again, you've been incredible!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •