CODEWITHSUNDEEP
vbaexcel
s.turn
s.turn

Reputation: 71

VBA - CommandButton Click sub - when the worksheet is created new each time

This feels like a stupid question but I can't figure it out, so here goes...

I have a workbook which, based on what's input into a userform, creates a new worksheet each time a command button on the userform is clicked, and deletes the old one.

That means that, although the new worksheet is always given the same name ("Results"), the sheet number increments each time; it's currently calling itself Sheet48 and next time I run it it will be Sheet49.

What I'd like to do is add a command button to that "Results" sheet, which users can click to take them to a different worksheet. I've got as far as adding some code into the userform which creates the command button itself on the "Results" worksheet. I haven't yet written the sub which would tell it to take the user to a different worksheet, but I'm confident that I could do this without much trouble.

My question is how I can assign the sub to the button, considering that the worksheet it's located on is not permanent? I thought the sub should go into a standard module, but all the examples I see for this kind of thing begin with "CommandButton1_Click", or "CommandButton2_Click", and are stored within the sheet object itself. It seems like these numbers 1 and 2 refer to the command button's order on a given worksheet, but presumably a sub on a standard module won't know which worksheet to look at.

I hope that makes sense - any helpful explanations will be much appreciated! Thanks.

EDIT: This is the code I'm using to create the command button and to (attempt to) assign the sub to it:

'Add a command button, which will allow users to jump to the full report

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=1025, Top:=130.5, Width:=160, Height:= _
        400).Select

ActiveSheet.CommandButton1.Caption = "Click here to see the original report"
ActiveSheet.CommandButton1.WordWrap = True
ActiveSheet.CommandButton1.OnAction = "Module1.ButtonForOriginal" 'the sub works in isolation, but this line isn't assigning it.

Can anyone see any issues there? The ButtonForOriginal sub procedure works in isolation, so it looks like I'm just not calling it properly?

EDIT 2 - I have now achieved the right effect with a different method - see my answer posted below if you're interested. Thanks to those who helped and set me on the right path!

Upvotes: 0

Views: 1696

Answers (3)

s.turn
s.turn

Reputation: 71

OK, I sort of figured it out. I had for some reason decided to use an ActiveX command button, instead of a plain old Form one. This seems to have made things more difficult. I don't understand the detail, but I have achieved the effect I was after by doing this instead:

   ActiveSheet.Buttons.Add(1025, 130.5, 160, 400).Select
       Selection.Characters.Text = "Click here to see the original report"
    With Selection.Characters(Start:=1, Length:=200).Font
        .Name = "Verdana"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Selection.OnAction = "ButtonForOriginal"

Upvotes: 0

Harassed Dad
Harassed Dad

Reputation: 4704

"CommandButton1_Click" should be an Event Procedure. They go into the module page connected with the sheet that the object sits on. So when you click on it, it knows which procedure to run, it's the one connected to the sheet the button is on.

Upvotes: 0

atykhan
atykhan

Reputation: 1

As i understand you are trying to add new sheet with new button, i would recommend using non-dependent buttons with hyperlinks to run your module.

Click insert shape - rectangle or so - and use Selection.OnAction = "YourWorkBook'!YOURSUB"

Upvotes: 0

Related Questions