• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with setting Buttons to set values on different sheet

D

dwrowe001

Member
Hi Everyone,
I am trying to set up 7 buttons on a sheet which when each are clicked would change the value of one cell on a different sheet to a certain value.

here is my attempt at trying to do this:
Code: 
Sub set_LX()

        If ActiveCell.Value = "A3" Then
            Worksheets("Predictions").Range("C6").Value = 3
        ElseIf ActiveCell.Value = "C3" Then
            Worksheets("Predictions").Range("C6").Value = 4
        ElseIf ActiveCell.Value = "E3" Then
            Worksheets("Predictions").Range("C6").Value = 5
        ElseIf ActiveCell.Value = "G3" Then
            Worksheets("Predictions").Range("C6").Value = 6
        ElseIf ActiveCell.Value = "I3" Then
            Worksheets("Predictions").Range("C6").Value = 7
        ElseIf ActiveCell.Value = "K3" Then
            Worksheets("Predictions").Range("C6").Value = 8
        ElseIf ActiveCell.Value = "M3" Then
            Worksheets("Predictions").Range("C6").Value = 9
           
        End If
End Sub

I have each button in a cell on sheet 3. Here is a screen shot of it:
upload_2017-10-24_18-33-58.png

I was thinking, for example, if I clicked on the button L3, that cell A3 would then become active, and make the IF Else statements would work... well, surprise surprise, they didn't.... sigh.

I know my feeble attempt at writing the above macro is nowhere near correct.. but I tried.. and I'm learning.. but it's frustrating.

Now I just need to know what exactly I did wrong. Can someone help and fix my errors and set me on the right path... thanks for any help.

Dave
 
Code is better than images of code. An attached file with code and controls and such is the best way to get on target help.

Add the other cases to suit. Assign this macro to each button in a Module.
Code: 
Sub fcb()
  With Worksheets("Predictions")
    Select Case Application.Caller  'Form Command Button's Name
      Case "Button 1"
        .[C6] = 3
      Case Else
    End Select
  End With
End Sub
 
Kenneth Hobson said:
Code is better than images of code. An attached file with code and controls and such is the best way to get on target help.

Add the other cases to suit. Assign this macro to each button in a Module.
Code: 
Sub fcb()
  With Worksheets("Predictions")
    Select Case Application.Caller  'Form Command Button's Name
      Case "Button 1"
        .[C6] = 3
      Case Else
    End Select
  End With
End Sub
Click to expand...

Well, it's not working for me.. would it be better if I use an ActiveX button? I'm not sure what I'm doing wrong.

You said assign the macro to each button in the module? That confuses me?
 
Kenneth Hobson said:
Code is better than images of code. An attached file with code and controls and such is the best way to get on target help.

Add the other cases to suit. Assign this macro to each button in a Module.
Code: 
Sub fcb()
  With Worksheets("Predictions")
    Select Case Application.Caller  'Form Command Button's Name
      Case "Button 1"
        .[C6] = 3
      Case Else
    End Select
  End With
End Sub
Click to expand...

Im assuming that for each of the buttons I change the "Case "Button 1"" name to the specific button name I have for each of my 7 buttons.. and also change the .[C6]=3 number 3 to the specific number for each button... right??
 
Why are you confused in post #5? The link in post #2 explains how to assign a Macro to a Form control.

Yes to post #6.

The name is in the top left of A1 where Names are when you select the control in the Developer tab.

If easier for you, you can use the "caption". e.g.
Code: 
Sub fcb2()
  With Worksheets("Predictions")
    ''Form Command Button's "caption" value.
    Select Case ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
      Case "L3"
        .[C6] = 3
      Case Else
    End Select
  End With
End Sub
 
Last edited:
Kenneth Hobson said:
Why are you confused in post #5? The link in post #2 explains how to assign a Macro to a Form control.

Yes to post #6.

The name is in the top left of A1 where Names are when you select the control in the Developer tab.

If easier for you, you can use the "caption". e.g.
Code: 
Sub fcb2()
  With Worksheets("Predictions")
    ''Form Command Button's "caption" value.
    Select Case ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
      Case "L3"
        .[C6] = 3
      Case Else
    End Select
  End With
End Sub
Click to expand...

I'm sorry Ken, I'm having trouble trying to figure this out. I've tried several different things none of which work for me. I am attaching my skimmed down file in hopes you can take a look and see if it works for you.

Can you please take a look and let me know if you can get it to work?
Thank you
Dave
 

Attachments

I went with fcb2 since your Control names are not linear like your caption names. Names are easily assigned rather than keeping the defaults as I explained earlier.

The first fcb2 did not work because I did not see the space character in your "caption" text. "L3" <> "L 3" so the Case was never true which I guess is what confused you.

When doing these things, one can go by Control Names, "Captions" as in fcb2, or maybe Tag values for ActiveX controls. IF one adopts a naming scheme as you did with the captions, one can iterate by others means too. e.g. Rather than the Case method, we could iterate an Array, and if the value is found, like caption in this case, we can then act. e.g. "L 3" has 3 at the end which is the value you are setting. So, many ways to do these if we have a smartly executed plan.

Be sure to Compile and Compile often to avoid errors that are hard to figure out later down the road.

Code: 
Sub fcb2()
  With Worksheets("Predictions")
    ''Form Command Button's "caption" value.
  Select Case ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
      Case "L 3"
        .[C6].Value = 3  '.Value is the default property.
      Case "L 4"
        .[C6] = 4
      Case "L 5"
        .[C6] = 5
      Case "L 6"
        .[C6] = 6
      Case "L 7"
        .[C6] = 7
      Case "L 8"
        .[C6] = 8
      Case "L 9"
        .[C6] = 9
      Case Else
    End Select
  End With
End Sub
 
Kenneth Hobson said:
I went with fcb2 since your Control names are not linear like your caption names. Names are easily assigned rather than keeping the defaults as I explained earlier.

The first fcb2 did not work because I did not see the space character in your "caption" text. "L3" <> "L 3" so the Case was never true which I guess is what confused you.

When doing these things, one can go by Control Names, "Captions" as in fcb2, or maybe Tag values for ActiveX controls. IF one adopts a naming scheme as you did with the captions, one can iterate by others means too. e.g. Rather than the Case method, we could iterate an Array, and if the value is found, like caption in this case, we can then act. e.g. "L 3" has 3 at the end which is the value you are setting. So, many ways to do these if we have a smartly executed plan.

Be sure to Compile and Compile often to avoid errors that are hard to figure out later down the road.

Code: 
Sub fcb2()
  With Worksheets("Predictions")
    ''Form Command Button's "caption" value.
  Select Case ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
      Case "L 3"
        .[C6].Value = 3  '.Value is the default property.
      Case "L 4"
        .[C6] = 4
      Case "L 5"
        .[C6] = 5
      Case "L 6"
        .[C6] = 6
      Case "L 7"
        .[C6] = 7
      Case "L 8"
        .[C6] = 8
      Case "L 9"
        .[C6] = 9
      Case Else
    End Select
  End With
End Sub
Click to expand...

Yup, that works... I'm so very thankful to you for helping me with this... you make it seem so simple. I try to figure it out but get so frustrated when it doesn't work. I'm gonna try to do something else with another macro, I will try to get it to work on my own, but If I can't you'll be hearing from me lol...
Thanks again!!
 
You must log in or register to reply here.
Back
Top