Attach button to sheet/ access from Private Sub

[YellowLabPro]

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

[Bob Phillips]

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?

[lucas]

Worksheet has to be Worksheets

is the attached what your asking for? It's still a macro.....

[YellowLabPro]

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

[lucas]

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....

[YellowLabPro]

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]

[lucas]

[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.

[YellowLabPro]

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

[lucas]

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.

[YellowLabPro]

Thanks Steve---- :-)

[lucas]

See this article by Johnske that tells you about using with statements to refer to objects.