Userform and checkboxes [solved]

[metropol]

Hi

I have never used userform before, and trying to put up a userform that a. select different autofilteroptions (I'm using checkboxes)
b. let the user to do som autofiltering of several workbooks (the workbooks are similar regarding structure and formats).
c. take a copy of the result from the autofilter result
d. paste this in a new workbook
e. go to next workbook, do b. and c. and paste this under the result from the last session.

Any suggestions for good links I could have an idea of how this could be done.

I have created the userform. But then I struggle a bit more. Should all coding be put under the commandButton1_Click() button (which is the Run Report button), or shoud I store the code in a class, function etc.

Help would be appreciated

 

[xenou]

This site is one that I have found to be very helpful. (I used it in conjunction with Walkenbach's Excel VBA Programming for Dummies to create my first userform). Note that the first part of this site gives an example of some wild messagebox code and then shows how to use a userform instead.

http://gregmaxey.mvps.org/Custom_MsgBox.htm

The example (like Walkenbach's intro chapter) uses the .Tag property of the userform as a value that is used for logic processing in the calling code (from a module): i.e., if .Tag is 1, do A, if .Tag is 2, do B, if .Tag is 3, do C, etc. The command buttons set this .Tag property before returning control to the calling routine.

You may want to adapt this procedure so that instead of reading the .Tag property, you capture the values of the checkboxes (checked or unchecked). After you Hide the userform, code execution returns to the module that was running when the form was shown. But you can still access the controls on the hidden form (If MyForm.chkMyCheckBox = True Then...). This is how I understand this anyway!

Generally, its best not to put lots of code in the command buttons, although it is possible to do that -- As you can see in the examples in the link, a button code might be no more than Me.Hide.

HTH

 

[metropol]

Thanks again Alexander for helping me out her.

Looking at the .tag, I can't figure out how to use different values in the following sub routine.

Ex.
I have 4 checboxes and 3 option buttons. When using me.tag, it will find and set the correct tag. But passing this back to the sub procedyre in module 1, only gives me opportunity to call one tag using userform1.tag
not userform1.tag1, userform.tag2 etc..

Then I was trying a variable when

Public sub checkbox1_click()
Me.Tag = 1
ichkbox1 = 1
end sub

then the variable is not passed to the sub in module 1, but the tag is.

 

[xenou]

Yes, you only have one .Tag value (use it well!)

I think you want to forget the .Tag property and read the values of the checkboxes directly...I believe these are True/False (checked or not checked)

You let the user check the boxes, then hide the form, then read these values in your code:

'A) CODE GOES TO USERFORM
'B) USER INTERACTS WITH THE FORM
'C) HIDE FORM AND CODE RETURNS TO THIS MODULE

'D) GET CHECKBOX VALUES INTO VARIABLES
If chkCheckBox1.Value = True Then
    myCopy = True
End If
If chkCheckbox2.Value = True Then
    myAutoFilter = True
End If
If chkCheckbox3.Value = True Then
    myPaste = True
End If

'E) HERE YOU CAN UNLOAD THE USERFORM, SINCE
'THE VALUES YOU NEED ARE IN VARIABLES

'F) CODE EXECUTION BASED ON CHECKBOXES
If myAutoFilter = True Then
    'do autofilter stuff
    
    If myCopy = True Then
        'do copy stuff
    End If
    
    If myPaste = True Then
        'Do paste stuff
    End If

End If

'G) END CODE

In practice with boolean values , you don't need the = true or = false:
If chkCheckBox2.value Then
If myAutofilter Then
etc.

Edit: Note, I think your issue is that you don't have either/or results - user's could do 2 of 3 actions, or 3 of 4. The only other way to do this would be to create a grid of all possible results and assign a single value for each "set" of actions which could relate to a single .Tag value (I.e, "1" being do A & B; "2" being do A,C, & D; "3" being do A,B, & D, etc.). A lot depends on the sequence of actions you have in mind (maybe the autofilter is one case, and the copy/paste is another which may or may not require the first action, etc.). You want to simplify your code by reducing the sequence to first doing the actions that are common to all possibilities, then adding on those actions that are optional.

I should correct myself, since the controls also have .Tag properties (there is only one .Tag property for the userform object).

HTH

Duh, I knew something was bothering me here:

'D) GET CHECKBOX VALUES INTO VARIABLES
myCopy = chkCheckbox1.Value
myAutoFilter = chkCheckbox2.Value
myPaste = chkCheckbox3.Value

 

[metropol]

Yes, you only have one .Tag value (use it well!)

I think you want to forget the .Tag property and read the values of the checkboxes directly...I believe these are True/False (checked or not checked)


In practice with boolean values , you don't need the = true or = false:
If chkCheckBox2.value Then
If myAutofilter Then
etc.

Edit: Note, I think your issue is that you don't have either/or results - user's could do 2 of 3 actions, or 3 of 4. The only other way to do this would be to create a grid of all possible results and assign a single value for each "set" of actions which could relate to a single .Tag value (I.e, "1" being do A & B; "2" being do A,C, & D; "3" being do A,B, & D, etc.). A lot depends on the sequence of actions you have in mind (maybe the autofilter is one case, and the copy/paste is another which may or may not require the first action, etc.). You want to simplify your code by reducing the sequence to first doing the actions that are common to all possibilities, then adding on those actions that are optional.

I should correct myself, since the controls also have .Tag properties (there is only one .Tag property for the userform object).

HTH

Duh, I knew something was bothering me here:

'D) GET CHECKBOX VALUES INTO VARIABLES
myCopy = chkCheckbox1.Value
myAutoFilter = chkCheckbox2.Value
myPaste = chkCheckbox3.Value

Hi Alexander
Thank you again.

You have observed my challenge correctly. I need to do different checks and do several actions. The creating of the grid is something I definetely have to do, to prevent spaggetti coding as well. I have numerous examples where I have managed that scenario.

I have implemented the code as u suggested, and it worked fine. Had to corrct the:

If chkCheckBox2.value Then

to

If myform.chkCheckBox2.value then

But I understood that after a short while.

No I will create the checks and work with the report output using the autofilter options, copy results to new sheet etc, etc.