Consulting

Results 1 to 11 of 11

Thread: Attach button to sheet/ access from Private Sub

  1. #1

    Attach button to sheet/ access from Private Sub

    In the idea of learning new methods I am wondering if I can attach a button on a worksheet that will activate another worksheet.
    I want to store the code in the worksheet, not as a macro. I am not familiar w/ worksheet too much yet.

    Here is what I have so far:
    [VBA]
    Private Sub Worksheet_Activate()
    Workbooks("TGSItemRecordCreatorMaster.xls").Worksheet("Record Creator").Activate
    End Sub
    [/VBA]

    I don't know if this idea is an allowable function of worksheet code???????

    Thanks,

    YLP

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    That's fine as long as you don't have an activate on the other sheet that sends you back.

    Where does this relate to the button?

  3. #3
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Worksheet has to be Worksheets

    is the attached what your asking for? It's still a macro.....
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  4. #4
    Hi lucas,
    The idea you sent back is correct, thank you.
    I am looking for the way things differ between a macro and worksheet code. You said it was still a macro, we are using Private Sub, I am not confused, confused, just not familiar w/ the differences. I may be way off base here, too. Can you fill me in if they are?

    Thx,
    YLP

  5. #5
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Hi YLP,
    I don't think it's as bad as you think...basically my thoughts on this are that code for a worksheet are private but so are some routines for a userform...

    they are still macro's and they will set off your security when you open excel and your security setting is set to high or medium...

    Basic difference here....someone will correct me if I'm wrong..is that you generally use ActiveX buttons, etc. to use a private sub in the worksheet code. If you use a button from the forms toolbar you can assign a public sub to the action of the button....
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  6. #6
    Thanks lucas,
    This last reply brought up two more things I am not familiar w/, Userform and ActiveX buttons. I have heard the term Userform thrown around but not sure what it is, and ActiveX buttons, no idea. If I were a betting man, I would bet ActiveX buttons was what you used in the example spreadsheet, yes?
    I will do some reading to find out more.

    I tried altering your code for the button to activate a particular sheet, I wanted to see if a particular cell could be made active. I have not been able to do so-- can you check this out please?

    [VBA]
    Option Explicit
    Private Sub CommandButton1_Click()
    'Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator").Activate
    With Worksheets("Record Creator").Activate
    .Range("A2").Select
    End With
    End Sub
    [/VBA]

  7. #7
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    [VBA]Private Sub CommandButton1_Click()
    'Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator").Activate
    Worksheets("Record Creator").Select
    With Worksheets("Record Creator")
    .Range("A2").Select
    End With
    End Sub[/VBA]
    A userform is created in the vbe. Go to insert and click on userform. All of the controls you can add from the toolbox are activeX controls....not just the buttons. You can also add activeX controls to the worksheet by making sure the Visual basic toolbar is visable and select your controls from there.

    You can add other controls to the sheet which are not ActiveX controls...they are added from the forms toolbar.

    right click on any toolbar while looking at a spreadsheet and make sure that Forms and/or Visual basic is checked.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  8. #8
    Thanks lucas,
    I will read up on that.
    Why do we Select instead of Activate? Which I have read that most of the time, Select is not necessary, but there are times when it is. The articles never dicuss those time, which now is one of these times

    and btw: lucas or Steve? You guys have way to many variables goin' on here

  9. #9
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Well you need to select or activate the desired sheet and then we are selecting a range just as an example. We could do other things with the range than select.

    I answer to either. Steve is my first name.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  10. #10
    Thanks Steve---- :-)

  11. #11
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    See this article by Johnske that tells you about using with statements to refer to objects.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

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