Enter Formula on button click

Hi,

I have some buttons named e.g Button 24, Button 23

I want some code that will enter into cell U23 = G2

So if button 24 is clicked U23 = G2
     if button 23 is clicked U23 = H2
     if button 22 is clicked U23  = I2

Thanks!
Seamus2626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
What kind of buttons - Form or ActiveX? Also do you mean you want the actual text "G2", or the value in cell G2?
0
Seamus2626Author Commented:
Its the form buttons and I want the value from G2

Thanks Rory!
0
Rob HensonFinance AnalystCommented:
Does it have to be buttons?

How about a drop down selection that allows you the user to choose options 1 to 3 or values 22 to 24 or whatever is required. This will then feed a value into a specific cell, lets assume B2.

You can then have the formula in U23 as:

=IF(B2="Option 1",I2,IF(B2="Option 2",H2,IF(B2="Option 3",G2,"No Option Chosen")))

Thanks
Rob H
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rory ArchibaldCommented:
You mean Form buttons on a sheet, rather than buttons on a userform, yes? (just to be sure)
0
Seamus2626Author Commented:
Form buttons on a sheet
0
Seamus2626Author Commented:
Thanks Rob, but due to multiple other things happening within a macro, that wouldn't be possible, the formula would have to activate when the button is clicked. Thanks
0
Rob HensonFinance AnalystCommented:
I take it you mean that "Button 23" or whichever is clicked would also have other actions and not just populating cell U23 with the required formula link.

Do your Buttons all have separate macros? If they all do pretty much the same thing apart from different values into that cell, it might be possible to combine the various macros into one and then use the "Case Select" statement to determine the variations between different buttons.

As always, a sample file would help.

Thanks
Rob
0
Rory ArchibaldCommented:
Assuming the same macro is called by all the buttons, you could just add this to it:

    Dim sCaller               As String
    Dim lColumn               As Long
    sCaller = Application.Caller
    lColumn = 31 - Val(Trim(Replace(sCaller, "Button", "", , , vbTextCompare)))
    Range("U23").FormulaR1C1 = "=R2C" & lColumn

Open in new window

0
Seamus2626Author Commented:
Thanks Rory, im getting Type Mismatch on

sCaller = Application.Caller

This is my full sub

Seamus

Sub Step_Thru_Managed()
    Dim slItem As SlicerItem
    Dim i As Long
    Dim sCaller As String
    Dim lColumn As Long
    
    Application.ScreenUpdating = False
    
    Sheets("PCM Sales Manager Dashboard").Select
    

    sCaller = Application.Caller
    lColumn = 31 - Val(Trim(Replace(sCaller, "Button", "", , , vbTextCompare)))
    Range("U23").FormulaR1C1 = "=R2C" & lColumn
    
  With ActiveWorkbook.SlicerCaches("Slicer_PCM_Global_Sales_Manager")
        '--deselect all items except the first
        .SlicerItems(1).Selected = True
        For Each slItem In .VisibleSlicerItems
            If slItem.Name <> .SlicerItems(1).Name Then _
                slItem.Selected = False
                
        Next slItem
      Call Print_PDF_Managed
        '--step through each item and run custom function
        For i = 2 To .SlicerItems.Count
            .SlicerItems(i).Selected = True
            .SlicerItems(i - 1).Selected = False
            Call Print_PDF_Managed
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

0
Rory ArchibaldCommented:
Is that routine assigned as a macro to the buttons?
0
Seamus2626Author Commented:
So button 23 has Step_Thru_Managed() assigned to it

So yes, one of the buttons, kicks that routine off

Thanks
0
Rory ArchibaldCommented:
Then you shouldn't get that error when you click Button 23. Can you post a workbook?
0
Seamus2626Author Commented:
Attached

Thanks
EE.xlsm
0
Seamus2626Author Commented:
My word, I have no idea why that is 34mb
0
Seamus2626Author Commented:
Heres a light version

So I need on clicking the button (or starting the routine) the code says

U23 =G2

So it could be placed in the onclick part?

Each button has a separate routine, so on clicking that button, you would say specifically - U23 = G2

On clicking the next button, its specifically U23 = I2 etc etc
Book3.xlsx
0
Seamus2626Author Commented:
Sorry Rob, missed your next comment, ya there is a sample file but its bloody big!
0
Seamus2626Author Commented:
Were you guys able to open the file?

Thanks
0
Rory ArchibaldCommented:
I can't see any buttons in your first file and there clearly isn't any code in the second file as it's an .xlsx. Care to try again?
0
Seamus2626Author Commented:
if you can open the file EE.xlsm, the buttons are on the top of the sheet M2 - W6
0
Seamus2626Author Commented:
ID: 40682946

The buttons are on that file Rory when I open, and the code behind it
0
Rory ArchibaldCommented:
Excel tries to recover the content, and there are no buttons when it does. I'll try another version.
0
Rory ArchibaldCommented:
OK it opens in 2010 and I don't get a Type Mismatch error on the Application.Caller line (I get all kinds of errors after that dues to the removed content).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author Commented:
Im a donkey, I was stepping through it in the routine as opposed to clicking the button.

Thanks Rory!
0
Seamus2626Author Commented:
Rory, I just checked if I click button number 2, it moves along to =H2

But because that's booked, it must move to G3 as you can see the way the spreadsheet is set up

So all the buttons ive listed below and what there corresponding value needs  to be

I can repost as a new q if this is a different q

thanks
Seamus

PDF Country (Managed) = G2
PDF Country (Booked) = G3
PDF Global SM (Managed) = H2
PDF Regional SM = I2
PDF in-country! (booked) = J2
PDF Sector (booked) = K3
PDF Sector (Managed) = K2
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.