Hi All............

I am making a small program in which I open a second Excel workbook and
extract a sheet from it into my Main workbook. I do not know the name of the
sheet, and assign it "UserSheetName" variable, and then I can populate and
manipulate data on that sheet at will.....and close te second
workbook......that part all works fine.

What I would like to do now, is from a macro in my Main workbook, I would
like to create a ChangeEvent macro into to that "UserSheetName" sheet that is
now located also in my Main workbook.

Is this a doable thing?

Vaya con Dios,
Chuck, CABGx3

Chip Pearson explains it all:
http://www.cpearson.com/excel/vbe.aspx

Make sure you read the instructions at the top (missing references and wrong
security settings will cause it to fail).

I stole this from Chip's site:

Option Explicit
Sub CreateEventProcedure()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim wks As Worksheet

Const DQUOTE = """" ' one " character

Set wks = Worksheets.Add
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(wks.CodeName)
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CreateEventProc("Activate", "Worksheet")
LineNum = LineNum + 1
.InsertLines LineNum, " MsgBox " & DQUOTE & "Hello World" & DQUOTE
End With
End Sub

I added the wks stuff and changed the name of the procedure.

===========
Is there anyway you could create a template worksheet that already has this code
-- either in the receiving workbook or even on your harddrive.

It may be easier (er, less prone to failure) to insert a new sheet from that
template, then copy data from the "real" sending sheet.



CLR wrote:
>
> Hi All............
>
> I am making a small program in which I open a second Excel workbook and
> extract a sheet from it into my Main workbook. I do not know the name of the
> sheet, and assign it "UserSheetName" variable, and then I can populate and
> manipulate data on that sheet at will.....and close te second
> workbook......that part all works fine.
>
> What I would like to do now, is from a macro in my Main workbook, I would
> like to create a ChangeEvent macro into to that "UserSheetName" sheet that is
> now located also in my Main workbook.
>
> Is this a doable thing?
>
> Vaya con Dios,
> Chuck, CABGx3

--

Dave Peterson

Dave has probably already interpreted your question correctly and answered
it (when I first read it I had a different take). If you are looking to trap
"UserSheetName" events (or even the events of any number of sheets in other
workbooks) directly in your own Main project shout again.

Regards,
Peter T

"CLR" <(E-Mail Removed)> wrote in message
news:86BC0D65-E6DF-496B-B775-(E-Mail Removed)...
> Hi All............
>
> I am making a small program in which I open a second Excel workbook and
> extract a sheet from it into my Main workbook. I do not know the name of
the
> sheet, and assign it "UserSheetName" variable, and then I can populate and
> manipulate data on that sheet at will.....and close te second
> workbook......that part all works fine.
>
> What I would like to do now, is from a macro in my Main workbook, I would
> like to create a ChangeEvent macro into to that "UserSheetName" sheet that
is
> now located also in my Main workbook.
>
> Is this a doable thing?
>
> Vaya con Dios,
> Chuck, CABGx3
>

Hmmmm......that stuff is so far over my head, I don't have enough time left
to even think about beginning to learn it..........I believe your second
solution is more my style........so I'll look in to the "template" method

Thanks Dave,

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote:

> Chip Pearson explains it all:
> http://www.cpearson.com/excel/vbe.aspx
>
> Make sure you read the instructions at the top (missing references and wrong
> security settings will cause it to fail).
>
> I stole this from Chip's site:
>
> Option Explicit
> Sub CreateEventProcedure()
> Dim VBProj As VBIDE.VBProject
> Dim VBComp As VBIDE.VBComponent
> Dim CodeMod As VBIDE.CodeModule
> Dim LineNum As Long
> Dim wks As Worksheet
>
> Const DQUOTE = """" ' one " character
>
> Set wks = Worksheets.Add
> Set VBProj = ActiveWorkbook.VBProject
> Set VBComp = VBProj.VBComponents(wks.CodeName)
> Set CodeMod = VBComp.CodeModule
>
> With CodeMod
> LineNum = .CreateEventProc("Activate", "Worksheet")
> LineNum = LineNum + 1
> .InsertLines LineNum, " MsgBox " & DQUOTE & "Hello World" & DQUOTE
> End With
> End Sub
>
> I added the wks stuff and changed the name of the procedure.
>
> ===========
> Is there anyway you could create a template worksheet that already has this code
> -- either in the receiving workbook or even on your harddrive.
>
> It may be easier (er, less prone to failure) to insert a new sheet from that
> template, then copy data from the "real" sending sheet.
>
>
>
> CLR wrote:
> >
> > Hi All............
> >
> > I am making a small program in which I open a second Excel workbook and
> > extract a sheet from it into my Main workbook. I do not know the name of the
> > sheet, and assign it "UserSheetName" variable, and then I can populate and
> > manipulate data on that sheet at will.....and close te second
> > workbook......that part all works fine.
> >
> > What I would like to do now, is from a macro in my Main workbook, I would
> > like to create a ChangeEvent macro into to that "UserSheetName" sheet that is
> > now located also in my Main workbook.
> >
> > Is this a doable thing?
> >
> > Vaya con Dios,
> > Chuck, CABGx3
>
> --
>
> Dave Peterson
>

Thanks for the response Peter, but I think I have bitten off more than I can
chew here........I will probably opt fot Dave's "template" solution, even tho
it will be a lot of work, it fits my mini-mind.

To clarify what I'm trying to do tho....I want to import a sheet into my
Main workbook and then write this code to that sheet...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$10" Then
Call FindBC
Else
End If
End Sub


What "FindBC" does, is actually Autofilter the data for whatever value is
entered into B10.........it works good in other workbooks where the sheet is
permanent, and I can hardcode.....just having trouble here where the sheet is
imported.

Vaya con Dios,
Chuck, CABGx3



"Peter T" wrote:

> Dave has probably already interpreted your question correctly and answered
> it (when I first read it I had a different take). If you are looking to trap
> "UserSheetName" events (or even the events of any number of sheets in other
> workbooks) directly in your own Main project shout again.
>
> Regards,
> Peter T
>
> "CLR" <(E-Mail Removed)> wrote in message
> news:86BC0D65-E6DF-496B-B775-(E-Mail Removed)...
> > Hi All............
> >
> > I am making a small program in which I open a second Excel workbook and
> > extract a sheet from it into my Main workbook. I do not know the name of
> the
> > sheet, and assign it "UserSheetName" variable, and then I can populate and
> > manipulate data on that sheet at will.....and close te second
> > workbook......that part all works fine.
> >
> > What I would like to do now, is from a macro in my Main workbook, I would
> > like to create a ChangeEvent macro into to that "UserSheetName" sheet that
> is
> > now located also in my Main workbook.
> >
> > Is this a doable thing?
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
>
>
>

Another approach might be to copy a 'template' sheet with code from your
Main wb (could be an addin) into the current wb.

I take it "FindBC" already exists in all workbooks which suggests those wb's
themselves were derived from a particular template. If so they could contain
an additional hidden dummy template sheet to be copied into same wb. But I'm
stretching into the realm of second guessing!

Regards,
Peter T

"CLR" <(E-Mail Removed)> wrote in message
news:A4044987-7644-4212-A789-(E-Mail Removed)...
> Thanks for the response Peter, but I think I have bitten off more than I
can
> chew here........I will probably opt fot Dave's "template" solution, even
tho
> it will be a lot of work, it fits my mini-mind.
>
> To clarify what I'm trying to do tho....I want to import a sheet into my
> Main workbook and then write this code to that sheet...
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Target.Address = "$B$10" Then
> Call FindBC
> Else
> End If
> End Sub
>
>
> What "FindBC" does, is actually Autofilter the data for whatever value is
> entered into B10.........it works good in other workbooks where the sheet
is
> permanent, and I can hardcode.....just having trouble here where the sheet
is
> imported.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Peter T" wrote:
>
> > Dave has probably already interpreted your question correctly and
answered
> > it (when I first read it I had a different take). If you are looking to
trap
> > "UserSheetName" events (or even the events of any number of sheets in
other
> > workbooks) directly in your own Main project shout again.
> >
> > Regards,
> > Peter T
> >
> > "CLR" <(E-Mail Removed)> wrote in message
> > news:86BC0D65-E6DF-496B-B775-(E-Mail Removed)...
> > > Hi All............
> > >
> > > I am making a small program in which I open a second Excel workbook
and
> > > extract a sheet from it into my Main workbook. I do not know the name
of
> > the
> > > sheet, and assign it "UserSheetName" variable, and then I can populate
and
> > > manipulate data on that sheet at will.....and close te second
> > > workbook......that part all works fine.
> > >
> > > What I would like to do now, is from a macro in my Main workbook, I
would
> > > like to create a ChangeEvent macro into to that "UserSheetName" sheet
that
> > is
> > > now located also in my Main workbook.
> > >
> > > Is this a doable thing?
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> >
> >
> >

If that's the event you want, you could drop the worksheet_change event from
every worksheet module and use the workbook_Sheetchange event (maybe...)

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal _
Target As Range)

If Target.Address = "$B$10" Then
Call FindBC
End If

End Sub

A couple of things to watch out for--you may have worksheets that don't need the
macro and the macro (FindBC) may need to know what sheet is being changed????

Option Explicit
Option Compare Text
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal _
Target As Range)

if target.address <> "$B$10" then
exit sub
end if

'option compare text means that upper/lower case differences
'aren't important
select case sh.name
case is = "header","instructions","skipme","nothere"
'do nothing
case else
Call FindBC(sh)
end select

End Sub

==============
Sub FindBC(wks as object)
msgbox wks.range("a1").address(external:=true)
End Sub



CLR wrote:
>
> Thanks for the response Peter, but I think I have bitten off more than I can
> chew here........I will probably opt fot Dave's "template" solution, even tho
> it will be a lot of work, it fits my mini-mind.
>
> To clarify what I'm trying to do tho....I want to import a sheet into my
> Main workbook and then write this code to that sheet...
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Target.Address = "$B$10" Then
> Call FindBC
> Else
> End If
> End Sub
>
> What "FindBC" does, is actually Autofilter the data for whatever value is
> entered into B10.........it works good in other workbooks where the sheet is
> permanent, and I can hardcode.....just having trouble here where the sheet is
> imported.
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Peter T" wrote:
>
> > Dave has probably already interpreted your question correctly and answered
> > it (when I first read it I had a different take). If you are looking to trap
> > "UserSheetName" events (or even the events of any number of sheets in other
> > workbooks) directly in your own Main project shout again.
> >
> > Regards,
> > Peter T
> >
> > "CLR" <(E-Mail Removed)> wrote in message
> > news:86BC0D65-E6DF-496B-B775-(E-Mail Removed)...
> > > Hi All............
> > >
> > > I am making a small program in which I open a second Excel workbook and
> > > extract a sheet from it into my Main workbook. I do not know the name of
> > the
> > > sheet, and assign it "UserSheetName" variable, and then I can populate and
> > > manipulate data on that sheet at will.....and close te second
> > > workbook......that part all works fine.
> > >
> > > What I would like to do now, is from a macro in my Main workbook, I would
> > > like to create a ChangeEvent macro into to that "UserSheetName" sheet that
> > is
> > > now located also in my Main workbook.
> > >
> > > Is this a doable thing?
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> >
> >
> >

--

Dave Peterson

Well, thanks very much guys, but I just this minute finished it up using the
"template" method....a hidden sheet set up with the changeevent macro and
copied into play, renamed, and re-hidden for later use......then the real
data beinc transferred to the blank sheet........kind of brute force, but all
works well

Thanks Dave and Peter for your time and thoughts......maybe will try
something more sophisticated next time.

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

> Hi All............
>
> I am making a small program in which I open a second Excel workbook and
> extract a sheet from it into my Main workbook. I do not know the name of the
> sheet, and assign it "UserSheetName" variable, and then I can populate and
> manipulate data on that sheet at will.....and close te second
> workbook......that part all works fine.
>
> What I would like to do now, is from a macro in my Main workbook, I would
> like to create a ChangeEvent macro into to that "UserSheetName" sheet that is
> now located also in my Main workbook.
>
> Is this a doable thing?
>
> Vaya con Dios,
> Chuck, CABGx3
>