option buttons in excel, automation 1

[Spyrios]

I'm making an excel file in Access. I've have all the particulars worked except for one. I need to have two option buttons created on the spreadsheet. How do I create and code those by code? I've created them in excel with the code to go along with them. So what I want to do works, I just need to find some way for Access to create what I want it to. Any suggestions?


Michael

 

[VBAjedi]

You should be able to find posts in this forum discussing inserting buttons with VBA. Once they exist, you have to put the desired code into their events. Here's a line of code that inserts a simple new Sub into the ThisWorkbook module of a workbook. It should at least get you pointed in the right direction:

Workbooks("NewWorkbookName").VBProject.VBComponents("ThisWorkbook").CodeModule.AddFromString _
"Private Sub Workbook_Activate()" & vbNewLine & _
" Call Update_Toolbar" & vbNewLine & _
"End Sub"

This is not a particularly easy or common task, but I'm fairly certain it can be done! Keep at it. . .


VBAjedi

 

[Spyrios]

You're friggin awesome. Thanks! I was really hoping that all of my work in coding the excel file wasn't going to go to waste, which is exactly what will happen if I don't get what you just helped me out with done.


Michael

 

[VBAjedi]

Just a thought - if this thing is going to be used in a controlled environment (i.e. you're not going to be mass distributing this project), a better option might be to create a template file with the buttons, code, etc. already setup. Then you just open a new file from your code using that template.

You just have to make sure the template is stored where everyone can get to it - either a commonly-accessible shared drive, or on every users PC.

VBAjedi