Dear All,
I am a beginner to VBA.
The situation is -:
When i click on a button in a existing sheet (A), a new sheet opens.
Apart from all the formatting etc, I am addding a Check box in this new sheet.
I want to assign a macro to this checkbox and want to store it in that workbook itself.
While i am able to access the checkbox using the following statement below -:
newWorkSheet.Shapes("Check Box 1").Select
In the Selection.OnAction I want to direct it to a peice of code which i have written but i am unable to transfer it to the new workbook.
Because this excel program has to be used by many people, i am not inclined to use the following code, becuase it requries them to enable the macro security setting of "Trust accesss to VBA project object model"
**** NOT WANTING TO USE THE FOLLOWING CODE*******
'Dim FName As String
'With Workbooks("AuctionRequestForm- Draft 3")
'FName = .Path & "\code.txt"
'.VBProject.VBComponents("Module1").Export FName
'End With
'ActiveWorkbook.VBProject.VBComponents.Import FName
**** NOT WANTING TO USE THE ABOVE CODE*******
Is there a way, I can dynamically type the peice of code in the new workbook?
I tried using ActiveWorkbook.VBProject.VBComponents.Add vbext_ct_StdModule, however, i am unable to do it.
Kindly help.
Bookmarks