Enter Formula on button click

What kind of buttons - Form or ActiveX? Also do you mean you want the actual text "G2", or the value in cell G2?

Its the form buttons and I want the value from G2

Thanks Rory!

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

You mean Form buttons on a sheet, rather than buttons on a userform, yes? (just to be sure)

Form buttons on a sheet

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

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

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

Select allOpen in new window

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

Select allOpen in new window

Is that routine assigned as a macro to the buttons?

So button 23 has Step_Thru_Managed() assigned to it

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

Thanks

Then you shouldn't get that error when you click Button 23. Can you post a workbook?

Attached

Thanks
EE.xlsm

My word, I have no idea why that is 34mb

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

Sorry Rob, missed your next comment, ya there is a sample file but its bloody big!

Were you guys able to open the file?

Thanks

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?

if you can open the file EE.xlsm, the buttons are on the top of the sheet M2 - W6

ID: 40682946

The buttons are on that file Rory when I open, and the code behind it

Excel tries to recover the content, and there are no buttons when it does. I'll try another version.

Im a donkey, I was stepping through it in the routine as opposed to clicking the button.

Thanks Rory!

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