i'm coding a dialog that has 2 different selection methods, but the
calculation is the same for both (i.e. different inputs, same
calculation method).

i'm trying to streamline it by creating one section called private sub
calculate(), and then calling this at the end of the other procedures.
this is in the same userform as the other ones.

i typed:


Code:
--------------------
call calculate
--------------------


at the end of both procedures, and only one of my selection methods
worked. the other appears to stop just before the procedure call (stops
at the last step before it).

what happened?

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Can't stop before executing this, at least not without an error. Perhaps you
have error handling that suppresses the error, either in main code or in
subroutine. Would need to see more of the code and know why you say it stops
before the sub - how do you know?

Try putting a breakpoint in the sub and you will find out if it is being
executed or not when it brings you into the debugger. Then maybe you can
find out what is happening.
--
- K Dales


"dreamz" wrote:


i'm coding a dialog that has 2 different selection methods, but the
calculation is the same for both (i.e. different inputs, same
calculation method).

i'm trying to streamline it by creating one section called private sub
calculate(), and then calling this at the end of the other procedures.
this is in the same userform as the other ones.

i typed:


Code:
--------------------
call calculate
--------------------


at the end of both procedures, and only one of my selection methods
worked. the other appears to stop just before the procedure call (stops
at the last step before it).

what happened?

thanks.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Hello dreamz,

When you say stops, did the call to the proccedure not execute, or did
it generate an error? You should also post the code for Calculate.

Thanks,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Leith Ross Wrote:
Hello dreamz,

When you say stops, did the call to the proccedure not execute, or did
it generate an error? You should also post the code for Calculate.

Thanks,
Leith Ross
thanks for the reply. when i say that it stops, i mean that the
procedure gets executed until the very end, but it doesn't call the
"calculate" procedure. no error at all.

here's the code for the part that calls. i've had to edit it for
security reasons.


Code:
--------------------

'Pull data from listWest
shTemp.Range("C1").Select
For i = 0 To listWest.ListCount - 1
If listWest.Selected(i) Then ActiveCell.Value = listWest.List(i)
If ActiveCell.Value = "" Then ActiveCell.Select
If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Select
Next i

'Pull data from listCentral
shTemp.Range("E1").Select
For i = 0 To listCentral.ListCount - 1
If listCentral.Selected(i) Then ActiveCell.Value = listCentral.List(i)
If ActiveCell.Value = "" Then ActiveCell.Select
If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Select
Next i

'Pull data from listSouth
shTemp.Range("G1").Select
For i = 0 To listSouth.ListCount - 1
If listSouth.Selected(i) Then ActiveCell.Value = listSouth.List(i)
If ActiveCell.Value = "" Then ActiveCell.Select
If ActiveCell.Value < "" Then ActiveCell.Offset(1, 0).Select
Next i

'Combine lists into one column
shTemp.Range("C1").CurrentRegion.Cut
If shTemp.Range("A1").Value = "" Then
shTemp.Range("A1").Select
ActiveSheet.Paste
Else
If shTemp.Range("A1").Value < "" Then
shTemp.Range("A1").End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If
End If

shTemp.Range("E1").CurrentRegion.Cut
If shTemp.Range("A1").Value = "" Then
shTemp.Range("A1").Select
ActiveSheet.Paste
Else
If shTemp.Range("A1").Value < "" Then
shTemp.Range("A1").End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If
End If

shTemp.Range("G1").CurrentRegion.Cut
If shTemp.Range("A1").Value = "" Then
shTemp.Range("A1").Select
ActiveSheet.Paste
Else
If shTemp.Range("A1").Value < "" Then
shTemp.Range("A1").End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
End If
End If

'Sort list alphabetically
shTemp.Activate
Range("A1").Select
Range("A1:A362").Sort Key1:=Range("A1"), Order1:=xlAscending

shTemp.Range("A1").Select
Range(Selection, Selection.End(xlDown)).Copy

Call Calculate
End Sub
--------------------


all it does is create a list from listboxes, then copies it.

and below is the code for the calculate procedure, again edited. it
doesn't do much but copy and paste certain ranges (that's how i set up
my workbook).

i can tell this isn't run because the previous procedure exits at
copying, and nothing else is populated (according to my calculate
procedure).


Code:
--------------------
Public Sub Calculate()
'Copy list to sheets

If chDep Then
shScale.Range("$B3").PasteSpecial
End If

If chHum Then
shScale.Range("$P3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shGrowth.Range("$B3").PasteSpecial
End If

If chHum Then
shGrowth.Range("$N3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shAccOpp.Range("$B3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shFormPos.Range("$B3").PasteSpecial
End If

If chHum Then
shFormPos.Range("$P3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shCap.Range("$B3").PasteSpecial
End If

If chHum Then
shCap.Range("$L3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shComp.Range("$B3").PasteSpecial
End If

If chHum Then
shComp.Range("$N3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shOpp.Range("$B3").PasteSpecial
End If

If chHum Then
shOpp.Range("$J3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shFit.Range("$B3").PasteSpecial
End If

If chHum Then
shFit.Range("$J3").PasteSpecial
End If

'If chTr then
'If chOO then

If chDep Then
shOppFit.Range("$B4").PasteSpecial
End If

If chHum Then
shOppFit.Range("$K4").PasteSpecial
End If

'Update bubble charts
If chDep Then
shOppFit.Activate
shOppFit.Range("C4:H4").Copy
shOppFit.Range("B4").End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.Offset(0, 5)).PasteSpecial
Range(Selection, Selection.End(xlUp)).PasteSpecial
End If

If chHum Then
shOppFit.Activate
shOppFit.Range("L4:Q4").Copy
shOppFit.Range("K4").End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.Offset(0, 5)).PasteSpecial
Range(Selection, Selection.End(xlUp)).PasteSpecial
End If

'Update single charts

'If chTr then
'If chOO then

If chDep Then
shOppFit.Range("H4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Range("K1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

shOppFit.Range("G4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Range("L1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If

If chHum Then
shOppFit.Range("Q4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Range("N1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

shOppFit.Range("P4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Range("O1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If

'Sort scores
shTemp.Activate
shTemp.Range("K1:L1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("L1"), Order1:=xlDescending

shTemp.Range("N1:O1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("O1"), Order1:=xlDescending

'Update other charts

If chDep Then
shCap.Activate
shCap.Range("B3:E3").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Activate
shTemp.Range("W1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
shTemp.Range("Y1").Value = "=VLOOKUP($W1,'MSA Abbr.'!$A$1:$B$417,2,FALSE)"
shTemp.Range("Y1").Select
ActiveCell.Copy
shTemp.Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial
End If

If chHum Then
shCap.Activate
shCap.Range("L3:O3").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp.Activate
shTemp.Range("AB1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
shTemp.Range("AD1").Value = "=VLOOKUP($AB1,'MSA Abbr.'!$A$1:$B$417,2,FALSE)"
shTemp.Range("AD1").Select
ActiveCell.Copy
shTemp.Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial
End If

'Update charts

If chDep Then
shOppFit.Activate
shOppFit.Range("B4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp2.Activate
shTemp2.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
shTemp2.Range("B1:F1").Copy
shTemp2.Range("A1").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, ActiveCell.Offset(0, 4)).PasteSpecial
Selection.Copy
Range(Selection, Selection.End(xlUp)).PasteSpecial
End If

If chHum Then
shOppFit.Activate
shOppFit.Range("K4").Select
Range(Selection, Selection.End(xlDown)).Copy
shTemp2.Activate
shTemp2.Range("H1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
shTemp2.Range("I1:N1").Copy
shTemp2.Range("H1").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, ActiveCell.Offset(0, 5)).PasteSpecial
Selection.Copy
Range(Selection, Selection.End(xlUp)).PasteSpecial
End If


Unload Me
Start.Activate
Application.ScreenUpdating = True

End Sub
--------------------


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Hello dreamz,

I looks like the variables in calculate are most likely going out of
scope, since you say it works with one call, but the other. If you are
not familiar the concept of scope and variable lifetimes, I'll explain
it briefly.

Scope has to do with the visibility (referencing) of a variable within
the program. A variable declared within a Sub or Function procedure for
a UserForm can only be seen by that procedure. If you want another Sub
or Function to be able to see (reference) that variable, you can do 2
things. One is to pass the variable to the other procedure, or make the
variable Public. To make the variable Public, available to all Subs and
Functions on the UserForm, you must place in the Declarations Section
of the User Form. Unlike local variables, the ones available only to
the Sub or Function itself, Public variables won't be re-initialized
when called again. So, you have to do that yourself. With a lot of
variables this can quickly become a programming nightmare.

A better option would be to place Calculate in a Project Module. This
will then broaden the scope to where any Sub or Function on a UserForm
or not can use it it. You will have to pass Calulate the variable
values chTr, chDep, chHum to it.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480621

K Dales Wrote:
Can't stop before executing this, at least not without an error.
Perhaps you
have error handling that suppresses the error, either in main code or
in
subroutine.
i doubt it, seeing as how i'm a beginner and don't know how to do that.
:D

Would need to see more of the code and know why you say it stops
before the sub - how do you know?
the calculate procedure copies a list and pastes it into certain
worksheets and vlookups to populate the sheets. i know the calculate
procedure isn't run because those sheets aren't populated. i can easily
see that the list hasn't been pasted.

Try putting a breakpoint in the sub and you will find out if it is
being
executed or not when it brings you into the debugger. Then maybe you
can
find out what is happening.
how do i do that?

i tried putting in something (msgbox "done") after the procedure call
and ran it. the message box popped up, without any error, which means
the the procedure finished, but still, nothing was copied over
(calculate procedure didn't work).

i don't get it. the calculate procedure works when it is called from
the other procedure. i don't see why it doesn't work here.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Leith Ross Wrote:
Hello dreamz,

I looks like the variables in calculate are most likely going out of
scope, since you say it works with one call, but the other. If you are
not familiar the concept of scope and variable lifetimes, I'll explain
it briefly.

Scope has to do with the visibility (referencing) of a variable within
the program. A variable declared within a Sub or Function procedure for
a UserForm can only be seen by that procedure. If you want another Sub
or Function to be able to see (reference) that variable, you can do 2
things. One is to pass the variable to the other procedure, or make the
variable Public. To make the variable Public, available to all Subs and
Functions on the UserForm, you must place in the Declarations Section
of the User Form. Unlike local variables, the ones available only to
the Sub or Function itself, Public variables won't be re-initialized
when called again. So, you have to do that yourself. With a lot of
variables this can quickly become a programming nightmare.

A better option would be to place Calculate in a Project Module. This
will then broaden the scope to where any Sub or Function on a UserForm
or not can use it it. You will have to pass Calulate the variable
values chTr, chDep, chHum to it.

Sincerely,
Leith Ross
i tried putting calculate into a different module, but then it stopped
working completely (probably because i didn't change anything).

but i think you may be onto something. chTr, chDep, etc. are titles for
my checkboxes. when i say later:


Code:
--------------------
If chTr Then
--------------------


i mean, if chTr is checked, then do this.

do i need to declare these? how would i go about doing this?


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Hello Dreamz,

The first line of Calculate should should look like this...

Public Sub Calulate(Byval chDep, ByVal chTr, Byval chHum)

If you have more varaiables add them to the list the same way. Thi
delaces the variables for you, so you don't need to have Dim statement
for each.

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48062

Leith Ross Wrote:
Hello Dreamz,

The first line of Calculate should should look like this...

Public Sub Calulate(Byval chDep, ByVal chTr, Byval chHum)

If you have more varaiables add them to the list the same way. This
delaces the variables for you, so you don't need to have Dim statements
for each.

Sincerely,
Leith Ross
thank you for your help. unfortunately, i get an error this time.

Compile error: Argument not optional

what went wrong?


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Hello Dreamz,

Sorry about that. I should have included an example of how to call the
procedure. It can be 2 different ways.

CALLING EXAMPLES:

This assumes you have assigned values to variables prior to the call.
This method requires the variables be in order.
chHum = -<value-
chTr = -<value-
chDep = -<value-
Call Calculate (chDep, chTr, chHum)

This method uses the variables names. This allows you to enter them in
random order.
Calculate chTr:= -<value-, chDep:= -<value-, chHum:= -<value-

All the values must be entered for either example. If you leave one
out, you get the error message "Argument not optional".

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Leith Ross Wrote:
Hello Dreamz,

Sorry about that. I should have included an example of how to call the
procedure. It can be 2 different ways.

CALLING EXAMPLES:

This assumes you have assigned values to variables prior to the call.
This method requires the variables be in order.
chHum = -<value-
chTr = -<value-
chDep = -<value-
Call Calculate (chDep, chTr, chHum)

This method uses the variables names. This allows you to enter them in
random order.
Calculate chTr:= -<value-, chDep:= -<value-, chHum:= -<value-

All the values must be entered for either example. If you leave one
out, you get the error message "Argument not optional".

Sincerely,
Leith Ross
i think we're getting somewhere.

my calculate procedure begins like this:


Code:
--------------------
Public Sub Calculate(ByVal chTr, ByVal chOO, ByVal chDep, ByVal chHum)
--------------------


and i used the second method.

it actually executed the procedure, but instead of pasting the list i
created, it pasted

calculate chTr:=true
and so on.

what happened?


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Hello Dreamz,

Where do the PasteSpecial values come from? Could be these need to
passed to procedure as well. Let me know, I don't see it in the code.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Leith Ross Wrote:
Hello Dreamz,

Where do the PasteSpecial values come from? Could be these need to
passed to procedure as well. Let me know, I don't see it in the code.

Sincerely,
Leith Ross
it's in the calculate procedure. the preceding procedures end by
copying a list, then calling the calculate procedure. that procedure
then pastes the list in different worksheets.

i must have messed something up. i can't get it to paste anything
anymore now.

thank you so much for taking the time to help me out.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Hello Dreamz,

Are the values chTr, ch00, chHum references to the Lists? If so, th
calling procedure will need to be modified slightly. Let me know.

Thanks,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48062

Leith Ross Wrote:
Hello Dreamz,

Are the values chTr, ch00, chHum references to the Lists? If so, the
calling procedure will need to be modified slightly. Let me know.

Thanks,
Leith Ross
they're not related to the original list at all. i know i'm not being
very clear, so allow me to explain.

i have one procedure (let's call it "listsub"). there are some
listboxes and a user can select certain items. listsub takes all those
selections and creates a compiled list on some worksheet. it then
copies that list and calls the other procedure, "calculate." and then
it ends here.

the calculate procedure simply takes the list that was copied and
pastes it into other sheets depending on what was chosen. so, for
example, if the checkbox for OO was ticked (in my code, it says "if
chOO then"), it will copy the list to the range specifed in the code,
and if the checkbox for Dep was ticked, it will copy the same list to
the range specified in the code (different from OO). if the checkbox
for OO is not ticked, it will not be copied to that range.

i hope that makes sense. if not, please let me know.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Leith Ross Wrote:
Hello Dreamz,

Your right about the changes I made to your code. Keep the code as is
and qualify the CheckBoxes. You didn't mention the name of UserForm the
checkboxes are on, so I didn't include it in the code changes. Am I
wrong in asssuming the checkboxes are on UserForm or do you have them
on aWorksheet(s)?

Thanks,
Leith
you are correct in your assumption. the checkboxes are on the userform
(named Dialog in my workbook).

here's what i did.

for the code:


Code:
--------------------
If chDep Then
--------------------


i tried changing it to:


Code:
--------------------
If Dialog.chDep Then
--------------------
that didn't work, so i tried:


Code:
--------------------
If Dialog.chDep.Value Then
--------------------
.. again, no change, so next:


Code:
--------------------
If Dialog.chDep.Value = True Then
--------------------


but to no avail. the macro did not paste my list anywhere (but those
cells were copied; i.e. the selection rectangle was moving).


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Hello Dreamz,

The code should be working as is. I have tried several variations of
the Copy and PasteSpecial to duplicate the problem, but I can't. I
created a UserForm with a checkbox, and placed the paste code into a
project module, and everything works. Which means there is something
else causing the problem. I look over your code again and see if I can
spot something.

Sincerely,
Leith Ross


--
Leith Ross


------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Leith Ross Wrote:
Hello Dreamz,

The code should be working as is. I have tried several variations o
the Copy and PasteSpecial to duplicate the problem, but I can't.
created a UserForm with a checkbox, and placed the paste code into
project module, and everything works. Which means there is somethin
else causing the problem. I look over your code again and see if I ca
spot something.

Sincerely,
Leith Ross
hello,

thank you very much for your help. i appreciate it.

i have no idea why it's not working. i can't see any problem with th
code itself (the calculate procedure works if i paste it into th
procedure instead of using "call").

i also just tried putting the code in a different module and callin
it:


Code
-------------------
Call Module3.Calculat
-------------------

neither procedure worked this time.

i guess i will never know what's wrong with this.

but thanks again. :

--
dream
-----------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...fo&userid=2646
View this thread: http://www.excelforum.com/showthread.php?threadid=48062

Hello Dreamz,

Is the list being copied successfully into Range("A1:A362") ? It hard
to understand why the code isn't working if the data is being moved
from the list to the cells that are to be copied to then paste on the
Worksheet determined by the checkbox. Without being able to see the
Workbook code in its entirety, I don't think we can resolve this
problem. Its often the little things that cause the biggest problems.
Sorry we couldn't fix it.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Leith Ross Wrote:
Hello Dreamz,

Is the list being copied successfully into Range("A1:A362") ? It hard
to understand why the code isn't working if the data is being moved
from the list to the cells that are to be copied to then paste on the
Worksheet determined by the checkbox.
the listsub procedure works until the procedure call. what i mean is,
the last step before the procedure call works. the list is created from
the listboxes and pasted into Temp!A1:A362. this works perfectly and in
fact, the list is copied.

the next step (call calculate) does not seem to work. the list is not
pasted into the other sheets. i don't see why this would fail, though.
all it's doing is checking to see if the checkbox was ticked. if it is,
then it pastes it. if it isn't, then it doesn't paste it. crazy excel.

Without being able to see the Workbook code in its entirety, I don't
think we can resolve this problem. Its often the little things that
cause the biggest problems. Sorry we couldn't fix it.

Sincerely,
Leith Ross
no worries. you've been extremely helpful and i've learned a lot just
from this thread. cheers!


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Take a look at
http://www.mvps.org/dmcritchie/excel/install.htm
which has a section on invoking macros and functions
from within a macro.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"dreamz" wrote in message
...

Leith Ross Wrote:
Hello Dreamz,

The code should be working as is. I have tried several variations of
the Copy and PasteSpecial to duplicate the problem, but I can't. I
created a UserForm with a checkbox, and placed the paste code into a
project module, and everything works. Which means there is something
else causing the problem. I look over your code again and see if I can
spot something.

Sincerely,
Leith Ross
hello,

thank you very much for your help. i appreciate it.

i have no idea why it's not working. i can't see any problem with the
code itself (the calculate procedure works if i paste it into the
procedure instead of using "call").

i also just tried putting the code in a different module and calling
it:


Code:
--------------------
Call Module3.Calculate
--------------------

neither procedure worked this time.

i guess i will never know what's wrong with this.

but thanks again. :)


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Hello Dreamz,

To test if the data is really on clipboard, select a cell on a
worksheet and select Paste Special from the Excel Edit menu and see if
it does Paste the date. If it does then problem lies with Calculate, if
not the problem is most likely in Listsub.

Thanks,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480621

Leith Ross Wrote:
Hello Dreamz,

To test if the data is really on clipboard, select a cell on a
worksheet and select Paste Special from the Excel Edit menu and see if
it does Paste the date. If it does then problem lies with Calculate, if
not the problem is most likely in Listsub.

Thanks,
Leith Ross
yes, it is. i can paste after the procedure finishes, which means
listsub works (because it's just supposed to copy).

i have no idea what's wrong with the calculate procedure, though.


@david: thanks. i'll have a look.


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621

i realized that the procedure wasn't being called, so i created a dummy
procedure (msgbox "blah") and tried calling it. didn't work. so i
tried calling it at different points in the procedure, and they
worked.

so i discovered that there was some bug at the end of the code, and it
turns out, there was some code that i had put in to display error
messages.

i fixed up the code and now it works.

thanks again, everyone, especially you, leith. :)


--
dreamz
------------------------------------------------------------------------
dreamz's Profile: http://www.excelforum.com/member.php...o&userid=26462
View this thread: http://www.excelforum.com/showthread...hreadid=480621