You have not confused me. I found your explanation very informative
however,
routine does not launch when I change the drop down. I even tried
copying
everything from the module into the appropriate worksheet and it still
does
not launch when the drop down selection is changed. I have changed
the
worksheet page to be
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row < 500 Then
[I inserted the lines that work when prompted by me that were
previously
in
the Module1. They are actually still in Module1. I just copied them
into
this worksheet.]
End If
End Sub
I don't think I had a hard time with anything else once I understood
what
was going on and how the commands worked. I still don't understand
fully
how
change events work and how they should be written and my book sucks in
this
area.
--
Thanks - K
:
Kristen,
I'm not as much of an expert as many of the other guys around here (I
learn
quite abit from this NG), but let me see if I can explain, hopefully
using
the correct terminology.
(More knowledgeable VBA experts, please feel free to correct me or add
to
this)
On the VBA side of things in an XL file you can have Object Modules,
?Regular Modules?, Class Modules, Forms, etc...
--Object Modules are connected to XL objects and usually have events
that
can be programmed to do things when ever they occur. Each sheet has
an
Object Module and the workbook itself has an object module.
--?Regular Modules? are modules where you would keep you
routines/procedures
(Subs) and User Defined Functions (UDF's). When ever you record a
macro
in
XL, it creates a Regular Module
--Class Modules...don't know much about them or what their purpose
is...still need to learn.
--Forms...kinda self explanatory I hope...people can create custom
forms
to
add functionality.
--Don't know what other kind of items can be created.
In the Project Explorer in the VBE, a workbook and all the different
modules
will be listed in a hierarchical structure like this ([Pi] = Project
Icon,
[Fi] = Folder Icon, [Si] = Sheet Icon, [Wi] = Workbook Icon, [Mi] =
Module
Icon):
[-][Pi] VBAProject (YourFileName.xls)
[-][Fi] Microsoft Excel Objects
[Si] Sheet1 (FirstSheetName)
...
[Si] Sheet{n} (LastSheetName)
[Wi] ThisWorkbook
[+][Fi] Forms
[-][Fi] Modules
[Mi] modCustomeUtilities
[Mi] Module1
[Mi] Module2
[+][Fi] Class Modules
(Forms, Modules, and Class Modules are optional...they will only be
there
if
your file contains items of these types)
Your code...
If Target.Column = 4 And Target.Row < 500 Then
CopyNotEmpty
End If
....needs to go in the Object Module of the sheet you have the Data
Validation (DV) dropdowns on (double-click the sheet in "Microsoft
Excel
Objects" for the file you are working on in Project Explorer),
specifically
the Worksheet Change event. So your code will look something like
this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row < 500 Then
CopyNotEmpty
End If
End Sub
Now if you wanted to, you could put your "CopyNotEmpty" routine in the
worksheet's object module. But that might be bad form. It will
definitely
work fine and won't cause any problems, but it probably is better form
to
have it in a Regular Module (from the sounds of your response, it
might
already be in one). Depending on how your routine is declared/defined
depends on where you can use it (what modules have access to it).
If your CopyNotEmpty routine is defined like this:
Sub CopyNotEmpty()
...
...
...
End Sub
....or...
Private Sub CopyNotEmpty()
...
...
...
End Sub
....then it can only be used in the module it is in.
So, if you have...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row < 500 Then
CopyNotEmpty
End If
End Sub
....in your worksheet module and your CopyNotEmpty routine in a
regular
module, you can't get to it in the worksheet module. In order to use
it,
change...
Sub CopyNotEmpty()
....or...
Private Sub CopyNotEmpty()
....to...
Public Sub CopyNotEmpty()
....or you could copy the CopyNotEmpty routine to the worksheet module
and it
would work there. But, once again, that is probably bad form. Better
to
leave it in a regular module and make it public.
Another thing, if the only time this code will be run is when cells
D1
499
on this certain sheet are changed, then having a separate sub routine
is
unnecessary. Just copy all of the code from the CopyNotEmpty sub to
the
Worksheet change event sub in the worksheet module. If it will need
to
be
run at other times, then go ahead and keep it separate.
HTH,
Please write back if I have confused you.
Conan
I tried putting your suggestion in another module and I'm not sure if
this
is
where it should go because it does not work there either. I put the
following in Module2:
If Target.Column = 4 And Target.Row < 500 Then
CopyNotEmpty
End If
CopyNotEmpty is the name of the Sub that works when prompted to run.
I
changed a selection in the drop down list that is located in cell D3
and
it
did not prompt anything. I'm fairly new to this and as I stated
before
my
book is not real good and I don't think Help in VBA is user friendly
for
beginners. Is there another place that I should put the code above.
In
the
same module that the orignal is written in, above or below where the
other
code is written?
--
Thanks - K
:
Kristen,
If Target.Column = 4 And Target.Row < 500 Then
<macro?
End If
Is that the exact syntax of your code in your change event
(including
"<macro?")? I'm not sure if you can include "<" & "?" in a macro
name.
I have a macro called "LoopCells". It looks something like this:
Sub LoopCells()
...
...
...
End Sub
If I were to call it from the change event using your code, it
would
look
like this:
If Target.Column = 4 And Target.Row < 500 Then
LoopCells
End If
Just replace "<macro?" with the exact name of your macro. If you
have
arguments for your macro, you would type a space between the macro
name
and
the first argument, then arguments would be separated my commas.
HTH,
Conan
