how to call subprocedure from within procedure?

[dreamz]

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.

 

[Guest]

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.

 

[Leith Ross]

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

 

[dreamz]

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

 

[Leith Ross]

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

 

[dreamz]

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.

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]

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?

 

[Leith Ross]

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

 

[dreamz]

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?

 

[Leith Ross]

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

 

[dreamz]

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?

 

[Leith Ross]

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

 

[dreamz]

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.

 

[Leith Ross]

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

 

[dreamz]

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.

 

[Leith Ross]

Hello Dreamz,

OK, I didn't realize that chTr, ch00 , etc. were checkboxes. From what
you describe we don't need to pass the values. we simply need to
qualify the object references. Confusing I know. I have updated the all
the Worksheet references for you. The only thing you need to do to the
code is qualify the checkboxes. For example: UserForm1.chDep.Value.
Have any more questions, let me know.


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

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

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

'If chTr then
'If chOO then

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

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

'If chTr then
'If chOO then

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

'If chTr then
'If chOO then

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

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

'If chTr then
'If chOO then

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

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

'If chTr then
'If chOO then

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

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

'If chTr then
'If chOO then

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

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

'If chTr then
'If chOO then

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

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

'If chTr then
'If chOO then

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

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

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

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

'Update single charts

'If chTr then
'If chOO then

If chDep Then
With Worksheets("shOppFit")
.Range("H4").Select
.Range(Selection, Selection.End(xlDown)).Copy

Worksheets("shTemp").Range("K1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

.Range("G4").Select
.Range(Selection, Selection.End(xlDown)).Copy

Worksheets("shTemp").Range("L1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With
End If

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

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

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

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

'Update other charts

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

If chHum Then
With Worksheets("shCap")
.Activate
.Range("L3:O3").Select
.Range(Selection, Selection.End(xlDown)).Copy
End With

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

'Update charts

If chDep Then
With Worksheets("shOppFit")
.Activate
.Range("B4").Select
.Range(Selection, Selection.End(xlDown)).Copy
End With

With Worksheets("shTemp2")
. Activate
.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.Range("B1:F1").Copy
.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 With
End If

If chHum Then
With Worksheets("shOppFit")
.Activate
.Range("K4").Select
.Range(Selection, Selection.End(xlDown)).Copy
End With

With Worksheets("shTemp2")
.Activate
.Range("H1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.Range("I1:N1").Copy
.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 With
End If


Unload Me
Start.Activate
Application.ScreenUpdating = True

End Sub

 

[dreamz]

Hello Dreamz,

OK, I didn't realize that chTr, ch00 , etc. were checkboxes. From wha
you describe we don't need to pass the values. we simply need t
qualify the object references. Confusing I know. I have updated the al
the Worksheet references for you. The only thing you need to do to th
code is qualify the checkboxes. For example: UserForm1.chDep.Value
Have any more questions, let me know.

i'm sorry it wasn't clear before.

if i understood the code right, you changed the sheet name t
worksheets("sheetname"), and you simplified a lot of the code with th
with-end with construction.

but what do you mean by qualify the checkboxes? instead of:


Code
-------------------
If chDep The
-------------------


change it to:


Code
-------------------
If Userform1.chDep.Value = True The
-------------------


? or did i completely misunderstand?

and i'm assuming i can remove all the other stuff we went throug
before

 

[Leith Ross]

Hello Dreamz,

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

Thanks,
Leit

 

[dreamz]

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:

 

[Leith Ross]

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