Dear Dave,
God damn amazing, all you have to do is say that the file is NOT an AddIn
??
Thanks, I'll try it very very soon.
Reminds me of the joke about 3 professors who wash up on an island after
a shipwreck.
3 cans of beer wash up with them.
The physicist takes a rock, smashes the top, loses half, and starts
drinking.
The engineer takes precise measurements and with a flick of his
fingernail opens his can.
They look over to the mathematician who just assumed the can was open,
drank it, and was already finished. Ba Da BOOM.
thanks again.
Neal
--
Neal Z
"Dave Peterson" wrote:
> I removed all the stuff I didn't care about <vbg> and this worked ok:
>
> Option Explicit
> Sub A__Copy_SHEET_TO_XLA()
>
> Dim AIwbk As Workbook
> Dim SourceWs As Worksheet
> Dim AfterWs As Worksheet
>
> Set SourceWs = ActiveSheet
> Set AIwbk = Workbooks("personal.xla")
>
> Set AfterWs = AIwbk.Sheets(AIwbk.Sheets.Count)
>
> AIwbk.IsAddin = False
> SourceWs.Copy After:=AfterWs
> AIwbk.IsAddin = True
>
> End Sub
>
>
> Neal Zimm wrote:
> >
> > 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
>
> --
>
> Dave Peterson
> .
>
|
Similar Threads
