Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Sheet to an AddIn
Hi All,
I've been building an addin for a while that will have multiple users. The user's workbook is referenced to the addin. I'm far from an expert on the addin object but with Walkenbach's book I've been exectuting code from the addin successfully. I'm trying to have the addin hold 'template' sheets that can be added to a user's workbook. I know I can copy a range, and formats, but I ALSO want the template to hold the vba code for selected sheet events like change and activa- tion. This has worked for me when copying from another workbook (not an addin workbook). Example: (The called proc is in the addin.) Private Sub Worksheet_Activate() Call Mgr_Activate 'this is the line I want in the addin template sheet. End Sub The proc below shows my work to date for a development tool to get the sheets into the addin. It works fine until the 'final' copy statements. Is it possible to do what I want without using the VBE objects? I'm nervous about trying to update a VBE module after looking at the objects involved, especially what's probably a class object for the sheet. I've next to no experience with classes. My fallback position is to have a regular workbook from which to copy if that is what's needed. I suppose I could also bring rm.xla back to rm.xls status, and then re-install the addin but I'd prefer not to do that time and again as new templates are needed. Thanks, Neal Z. Sub A__Copy_SHEET_TO_XLA() Const Title = "Copy WrkSht ** TO ** RM.XLA, " Const Cr = vbCr Const Cr2 = Cr & Cr Const Tb = vbTab Dim AIwbk As Workbook Dim SourceWs As Worksheet Dim Ix As Long Dim AfterWs As Worksheet 'mainline start ' This loop did not show the name of my addin, why not? ' The addin was open at the time. See Set AIwbk below. ' this was an experiment, not part of my main problem. ' For Ix = 1 To AddIns.Count ' If vbYes = MsgBox("Quit ?", vbYesNo + vbDefaultButton2, _ ' "Na: " & AddIns(Ix).Name Then ' Exit For ' End If ' Next Ix ' MsgBox "AddIn index " & Ix, , "AddIns Count " & AddIns.Count Set SourceWs = ActiveSheet Set AIwbk = Workbooks("rm.xla") If bWsExistF(SourceWs.Name, AIwbk) Then If vbNo = MsgBox(SourceWs.Name & " exists in " & AIwbk.Name _ & Cr2 & "Confirm Sheet Deletion, Click NO to Quit Copy", _ vbYesNo + vbDefaultButton2, Title & SourceWs.Name) Then Exit Sub End If 'the actual delete has yet to be implemented. End If Set AfterWs = AIwbk.Sheets(AIwbk.Sheets.Count) If vbNo = MsgBox("Confirm Copy ?? " & SourceWs.Name _ & Cr2 & "FROM:" & Tb & SourceWs.Parent.Name _ & Cr & "TO:" & Tb & AIwbk.Name & " After: " & AfterWs.Name _ & Cr2 & "Click NO to Quit Copy", _ vbYesNo + vbDefaultButton2, Title & SourceWs.Name) Then Exit Sub End If 'SourceWs.Copy After:=AfterWs 'this line crapped out AIwbk.Sheets.Add After:=AfterWs 'Sheet was added 'line below crapped out. I know I can copy cells from the source to 'to the addin, but that doesn't get me the 'copies' of the event calls 'for the template sheet. 'thought this might equate to a copy, guess not. Set AIwbk.Sheets(AIwbk.Sheets.Count) = SourceWs Exit Sub 'mainline end End Sub -- Neal Z |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba addin to copy a workbook | Excel Programming | |||
Addin sheet | Excel Programming | |||
AddIn to delete sheet | Excel Programming | |||
Cannot copy addin locally error? | Excel Programming | |||
Copy sheet from addin | Excel Programming |