before double click event of worksheet created by program

[RobynC]

Sorry (best title I could think of!)

My program(which is an add-in, or will be when I've finished writing it) creates a new worksheet called "Groups Summary" I would like this sheet to be locked so that the user can not modify anything on it. I would also like that if they double click a cell this will launch a userform if based on certain conditions.

I believe this code would have to go in the worksheet itself, so the question is how do I get the macro to enter code into a worksheet that it creates?

Is there a way to write it in a standard module and then automatically copy it when the worksheet is created?

[mikerickson]

Here is one way to write to the code module of a newly created worksheet. Opening the VBIDE Library will help with writting similar code.

Please Login or Register  to view this content.

[RobynC]

Thanks, not tried it yet, but looks just what I'm after.

One furtehr question, what's the VBIDE library?

[mikerickson]

(I'm doing this from memory)
In the VB Editor, from the Tools menu, select "Refference"(?), and you will be given a long list of librarys to open.
"Microsoft Visual Basic Extensibility Library" (?) is the one that you want.
What it does is give you access to other properties and objects.
For example, with the VBIDE not open "codemodule" will return nothing from the Object Browser. With it open, then "codemodule" will be displayed along with several properties that are useful for your project.

[RobynC]

Hi, Sorry for slow reply, i was on holiday. With some help, (see http://www.excelforum.com/showthread...12#post1844512) I now have code that works in the worksheet to call the userform from the xla. However one of the lines of code I need to transfer already contains quotation marks and hense produces a syntax error. Can anyone help with this?

Please Login or Register  to view this content.

Below is the code as I would like it to apprear in the worksheet, mainly in here as I think it will be easier to read than the above:

Please Login or Register  to view this content.

[mikerickson]

Please Login or Register  to view this content.

[RobynC]

Hi again,

I am still getting problems with the code in the previous posts, the first line (Private Sub .....) transfers to the worksheet fine, the next line causes Excel to crash. Any ideas what is causing this?

Mikerickson, I have updated the code to include your correction, and the line is no longer concidered a syntax error so thanks for that

[RobynC]

Hi again,

Okay am getting onwhere with this, basically the code makes Excel crash. I have been looking at the following links and it seems to be either a problem with CodeName or possibly with the company anti virus software (although the latter would do more than just crash Excel so I don't think this is the problem.

http://www.cpearson.com/excel/vbe.aspx
http://www.mrexcel.com/archive2/42500/49267.htm
http://www.vbaexpress.com/forum/show...=transfer+code

below are the attempts I have made at this code, all fairly similar, and all will insert the first line of code into the worksheet and then crash, either when the program moves to the next line of code, or for no apparent reason after a short delay even if the program is not advanced to the next line.


First Attempt (from mikerickson's code)

Please Login or Register  to view this content.

2nd attempt from first link given

Please Login or Register  to view this content.

3rd attempt, adapted from 2nd link

Please Login or Register  to view this content.

In the second link Tom Urtis provides a method for adfding code that bypasses the use of the codename, but I do not understand how this is working so have not tried it.

if anyone could help it would be greatly appreciated, just to clarify the problem, I want to transfer the code given at the end of my post at 12:15 (30/10/07, post 1844515) to a new worksheet that is created by the addin.

Thanks in advance for any adivce, I'm really lost on this.

PS realised I don't need to unprotect the sheet as it is not protected at this point, so ignore that line of code, Regards

[shg]

Rather than writing the code line by line, might it be easier to import it from an existing .bas file?

Edit: If you have not already spent time there, I think Chip Pearson's Programming the VBA Editor is the definitive reference: http://www.cpearson.com/excel/vbe.aspx.

[RobynC]

Hey shg, thanks for the suggestion.

i don't actually know what a .bas file is, so if you have time to explain that would be great, otherwise no worries as I have managed to do it by creating a worksheet template and then inserting that rather than trying to add the code to a blank sheet. This appears to be working and is the method suggested by the ozgrid people.

[shg]

A bas file is just a text file that Excel recognizes as containing code.

And yes, for code in a sheet module, a sheet template is just the ticket.