drop down change event
 

I have a column (D) of cells that the end user will select from a drop down
validation list. I would like when this drop down is changed, that the
change will prompt the macro. I have tested the rest of the macro and it
works but I can't get the change event to work. The code I have for the
change event is:

If Target.Column = 4 And Target.Row < 500 Then
<macro?
End If

I am fairly new to VBA and am very excited that I was able to write the
other parts of the program and get them to work. I'm finding the book I
bought leaves alot to be desired and if the example is not exactly what you
need, the explanation for how the example works is not there. I would really
appreciate anyone's help.
--
Thanks - K

drop down change event
 

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






"Kristen" wrote in message
...
I have a column (D) of cells that the end user will select from a drop down
validation list. I would like when this drop down is changed, that the
change will prompt the macro. I have tested the rest of the macro and it
works but I can't get the change event to work. The code I have for the
change event is:

If Target.Column = 4 And Target.Row < 500 Then
<macro?
End If

I am fairly new to VBA and am very excited that I was able to write the
other parts of the program and get them to work. I'm finding the book I
bought leaves alot to be desired and if the example is not exactly what
you
need, the explanation for how the example works is not there. I would
really
appreciate anyone's help.
--
Thanks - K

drop down change event
 

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


"Conan Kelly" wrote:

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






"Kristen" wrote in message
...
I have a column (D) of cells that the end user will select from a drop down
validation list. I would like when this drop down is changed, that the
change will prompt the macro. I have tested the rest of the macro and it
works but I can't get the change event to work. The code I have for the
change event is:

If Target.Column = 4 And Target.Row < 500 Then
<macro?
End If

I am fairly new to VBA and am very excited that I was able to write the
other parts of the program and get them to work. I'm finding the book I
bought leaves alot to be desired and if the example is not exactly what
you
need, the explanation for how the example works is not there. I would
really
appreciate anyone's help.
--
Thanks - K

drop down change event
 

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:D499
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







"Kristen" wrote in message
...
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


"Conan Kelly" wrote:

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






"Kristen" wrote in message
...
I have a column (D) of cells that the end user will select from a drop
down
validation list. I would like when this drop down is changed, that the
change will prompt the macro. I have tested the rest of the macro and
it
works but I can't get the change event to work. The code I have for the
change event is:

If Target.Column = 4 And Target.Row < 500 Then
<macro?
End If

I am fairly new to VBA and am very excited that I was able to write the
other parts of the program and get them to work. I'm finding the book
I
bought leaves alot to be desired and if the example is not exactly what
you
need, the explanation for how the example works is not there. I would
really
appreciate anyone's help.
--
Thanks - K

drop down change event
 

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


"Conan Kelly" wrote:

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:D499
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







"Kristen" wrote in message
...
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


"Conan Kelly" wrote:

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






"Kristen" wrote in message
...
I have a column (D) of cells that the end user will select from a drop
down
validation list. I would like when this drop down is changed, that the
change will prompt the macro. I have tested the rest of the macro and
it
works but I can't get the change event to work. The code I have for the
change event is:

If Target.Column = 4 And Target.Row < 500 Then
<macro?
End If

I am fairly new to VBA and am very excited that I was able to write the
other parts of the program and get them to work. I'm finding the book
I
bought leaves alot to be desired and if the example is not exactly what
you
need, the explanation for how the example works is not there. I would
really
appreciate anyone's help.
--
Thanks - K

drop down change event
 

Kristen,

First, lets see if your worksheet change event is firing. Change...


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


....to...


Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Worksheet change event is working correctly"
End Sub


Now, make any change to any cell on the worksheet (enter any value/text into
any cell). When ever you make a change, a message box with "Worksheet
change event is working correctly" will pop up. Make sure to test cells
that are in D1:D499, but also check other cells as well.

If that is not working, then you have the code in the wrong place or the
events are not firing for some reason.

Try that and let me know what is going on.

Conan





"Kristen" wrote in message
...
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


"Conan Kelly" wrote:

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:D499
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







"Kristen" wrote in message
...
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


"Conan Kelly" wrote:

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






"Kristen" wrote in message
...
I have a column (D) of cells that the end user will select from a
drop
down
validation list. I would like when this drop down is changed, that
the
change will prompt the macro. I have tested the rest of the macro
and
it
works but I can't get the change event to work. The code I have for
the
change event is:

If Target.Column = 4 And Target.Row < 500 Then
<macro?
End If

I am fairly new to VBA and am very excited that I was able to write
the
other parts of the program and get them to work. I'm finding the
book
I
bought leaves alot to be desired and if the example is not exactly
what
you
need, the explanation for how the example works is not there. I
would
really
appreciate anyone's help.
--
Thanks - K

drop down change event
 

The message box did fire. It fired for any cell that had a change. That
was a good idea. At least now I know that I was entering it in the correct
area of VBE.

I changed to


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row < 500 Then
MsgBox "Column D is Working"

End If

End Sub

and that worked. Now I removed the MsgBox Line and substituted the Sub
name. It worked as well. I don't know what was wrong. I suspect I was
actually having a problem wiht VBE. I noticed there were 2 projects open
with the same file name. I had a heck of a time getting the 2nd one closed
after I closed the file. It seems to work now that I have it closed.

Thanks for all of your help. I am on a long term temp job and am trying to
justify my permanent employment. Once I get this thing polished and pretty
now that the hard part is over, I think I will be a lot closer.

Thanks again!


-
Thanks - K


"Conan Kelly" wrote:

Kristen,

First, lets see if your worksheet change event is firing. Change...


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


....to...


Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Worksheet change event is working correctly"
End Sub


Now, make any change to any cell on the worksheet (enter any value/text into
any cell). When ever you make a change, a message box with "Worksheet
change event is working correctly" will pop up. Make sure to test cells
that are in D1:D499, but also check other cells as well.

If that is not working, then you have the code in the wrong place or the
events are not firing for some reason.

Try that and let me know what is going on.

Conan





"Kristen" wrote in message
...
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


"Conan Kelly" wrote:

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:D499
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







"Kristen" wrote in message
...
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


"Conan Kelly" wrote:

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

drop down change event
 

Kristen,

Glad that I could help.

Conan



"Kristen" wrote in message
...
The message box did fire. It fired for any cell that had a change.
That
was a good idea. At least now I know that I was entering it in the
correct
area of VBE.

I changed to


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row < 500 Then
MsgBox "Column D is Working"

End If

End Sub

and that worked. Now I removed the MsgBox Line and substituted the Sub
name. It worked as well. I don't know what was wrong. I suspect I was
actually having a problem wiht VBE. I noticed there were 2 projects open
with the same file name. I had a heck of a time getting the 2nd one
closed
after I closed the file. It seems to work now that I have it closed.

Thanks for all of your help. I am on a long term temp job and am trying
to
justify my permanent employment. Once I get this thing polished and
pretty
now that the hard part is over, I think I will be a lot closer.

Thanks again!


-
Thanks - K


"Conan Kelly" wrote:

Kristen,

First, lets see if your worksheet change event is firing. Change...


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


....to...


Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Worksheet change event is working correctly"
End Sub


Now, make any change to any cell on the worksheet (enter any value/text
into
any cell). When ever you make a change, a message box with "Worksheet
change event is working correctly" will pop up. Make sure to test cells
that are in D1:D499, but also check other cells as well.

If that is not working, then you have the code in the wrong place or the
events are not firing for some reason.

Try that and let me know what is going on.

Conan





"Kristen" wrote in message
...
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


"Conan Kelly" wrote:

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:D499
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







"Kristen" wrote in message
...
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


"Conan Kelly" wrote:

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