Printranges according to textbox and checkbox.

What i need is:

I have 10 Textbox's (for now) and 10 checkbox's.

If some value is in textbox1, then checkbox 1 is check up.
Textbox2 not empty, then checkbox2 = true
etcetc
In some case i will pre define some textbox's value according to 1 cell. But others, i need to open userform and put manually the textbox's values, and the checkbox's need to get true value.

According to checkbox's selection, export all print ranges, each checkbox has a defined range.

I attach a workbook, although is may have some errors, i can't evolve...
Hope someone can help me out.

Hi,brainzlp

try this

Hello simcode, thanks in advance for your help and attention.
About the workbook it's in a good way, although i have some concerns for what a saw.
- No pdf was exported, saved or opened.
- I need the textbox's values in the sheet "SECÇÕES PASSAGEM" , i mean... put the value of textbox1, in the 1st field open, then textbox2, etc etc.
- According to select cases, i need to input values in some textbox's , is it possible, where can i put it?
- According to 1 cell value, if "ADMIN" open the form to make manual changes, else just execute the default printranges defined in the early select cases.

Hope you can help me out, it's really important
Check file in attach

First off, I'll say sorry for my bad English.
I know, you need INPUT & OUTPUT from the userform. You can find samples from sub FormShow in workbook I submitted.
Input code

    frm.TCSCol.TextBoxValues = WorksheetFunction.Transpose(Sheet1.Range("A1:B2"))
    frm.TCSCol.CheckBoxValues = WorksheetFunction.Transpose(Sheet1.Range("D1:E2"))

Output

        v = frm.TCSCol.TextBoxValues
        Sheet1.Range("A5:B5").Resize(UBound(v, 2)) = WorksheetFunction.Transpose(v)
        
        v = frm.TCSCol.CheckBoxValues
        Sheet1.Range("D5:E5").Resize(UBound(v, 2)) = WorksheetFunction.Transpose(v)

It's just an example. You can modify it if necessary.

Hello, i understand perfectly your english.

I already saw that examples, although i need all values, even if empty, save as empty.

- No pdf was exported, saved or opened.
- I need the textbox's values in the sheet "SECÇÕES PASSAGEM" , i mean... put the value of textbox1, in the 1st field open, then textbox2, etc etc.
- According to select cases, i need to input values in some textbox's , is it possible, where can i put it?
- According to 1 cell value, if "ADMIN" open the form to make manual changes, else just execute the default printranges defined in the early select cases.

Can you please help me out with the this issues on top?
The workbook sent is not taking me the PDF, nor the message etc .

It's midnight in china, i'll reply tomorrow

Ok simcode, thanks for your help. Hopefully you can help me out with that issues.
It's really urgent and important for my work.

See you tomorrow

Hello Mr Simcode,

About this 4 things:
1- No pdf was exported, saved or opened.
2- I need the textbox's values in the sheet "SECÇÕES PASSAGEM" , i mean... put the value of textbox1, in the 1st field open, then textbox2, etc etc.
3- According to select cases, i need to input values in some textbox's , is it possible, where can i put it?
4- According to 1 cell value, if "ADMIN" open the form to make manual changes, else just execute the default printranges defined in the early select cases.

The number 3, i was going to put it in the userform_initialize, although don't know if it's the best option.
Waiting forward for your reply about this 4 issues as soon as you can.

Thanks again for your precious help

It seem like the order in sheet "SECÇÕES PASSAGEM" is different from the userform.
For the Input & Output, the sub ShowForm has explained it.

Point 3
I have explained it in Sub ShowForm in module "ImprimirPdf"
Point 4
If the form doesn't show, why did you need it "run in background", passing the data to the sub needed directly.

Originally Posted by simcode

Point 3
I have explained it in Sub ShowForm in module "ImprimirPdf"
Point 4
If the form doesn't show, why did you need it "run in background", passing the data to the sub needed directly.

Originally Posted by simcode

Point 3
I have explained it in Sub ShowForm in module "ImprimirPdf"
Point 4
If the form doesn't show, why did you need it "run in background", passing the data to the sub needed directly.

I need the file to export to pdf according to checkboxs selected which isnt doing. (really important)

About point 3, what i need is to write in textbox values once showform is executed, i must do this in userform initialize and should work.

About point 4, i need to run on background so other users dont change checkboxs values and print the orders wrong. Only admin has privileges to change and then confirm/validate the rest is standard and defined in point 3.

Hope to hear from you asap

If someone can help me exporting to pdf according to values in checkboxs i would appreciate. I need to have this soon and no clue on how to do it

Simcode do you know how to export printranges according to checkboxs?

I'll have a try if you can supply sufficent data in your book.

Originally Posted by simcode

I'll have a try if you can supply sufficent data in your book.

What data do you need more?

 Const sRANGE_1      As String = "J1:AB75"               '-> checkbox1
    Const sRANGE_2      As String = "J76:AB150"             '-> checkbox2
    Const sRANGE_3      As String = "J151:AB225"            '-> checkbox3
    Const sRANGE_4      As String = "J226:AB300"            '-> checkbox4
    Const sRANGE_5      As String = "J301:AB375"            '-> checkbox5
    Const sRANGE_6      As String = "J376:AB450"            '-> checkbox6
    Const sRANGE_7      As String = "J451:AB525"            '-> checkbox7
    Const sRANGE_8      As String = "J1:AB525"              '-> checkbox8
    Const sRANGE_9      As String = "J1:AB525"              '-> checkbox9
    Const sRANGE_10     As String = "J1:AB525"              '-> checkbox10
    Const sRANGE_11     As String = "J1:AB525"              '-> checkbox11

Consider those ranges according to checkbox's numbers.

For the message do be displayed:

sMessage = "As seguintes secções foram impressas:"

    'vaCheckBoxes = frm.SelectedCheckBoxes

    For iCheckBoxNo = LBound(vaCheckBoxes, 1) To UBound(vaCheckBoxes, 1)

        If vaCheckBoxes(iCheckBoxNo, 1) = True Then

            If rSelectedRanges Is Nothing Then
                  Set rSelectedRanges = vaPrintRanges(iCheckBoxNo)
            Else: Set rSelectedRanges = Union(rSelectedRanges, _
                                              vaPrintRanges(iCheckBoxNo))
            End If

            If vaCheckBoxes(iCheckBoxNo, 2) <> vbNullString Then
                sMessage = sMessage & vbLf & vbLf & vbTab & vaCheckBoxes(iCheckBoxNo, 2)
            End If

        End If

    Next iCheckBoxNo

    If Not rSelectedRanges Is Nothing Then

        Set wks = vaPrintRanges(1).Parent
        sFileName = wks.Range(Celula_Nr_Entrada_OP).Value & ".pdf"

        On Error Resume Next
            Kill PATH_OP & sFileName
        On Error GoTo 0

        rSelectedRanges.ExportAsFixedFormat Type:=xlTypePDF, _
                                            Filename:=PATH_OP & sFileName, _
                                            OpenAfterPublish:=True

        MsgBox sMessage
    End If

You have all this information in the module imprimirPDF

If anything more you need, let me know, i really need this with urgency please

Hope this file is your desired.

Thanks for your help.

Although its not like that that we were looking for..

1) It's not always 75 rows, it can be different, that's why we should define for each checkbox 1 range, cuz it will change..
2)Then i need after pdf exportation, to show in message which checkbox's were printed, (show checkbox's caption) like i had earlier, the code i sent you.

3)Can you please remove in the code what's not being used as well?

4) And also tell me other thing: you have as default, textbox1.value = "test" , textbox3.value = "abc" , and checkbox7.value = true , where is that, how can i remove it?

Please answer me all questions, Thanks again

I have to sleep. see u.

Textbox values can be input in sub FormShow

Public Sub ShowForm()
    Dim frm As Imprimir_checkbox
    Dim v   As Variant
    
    
    Set frm = New Imprimir_checkbox

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Here for textboxes input
    'frm.TCSCol.TextBoxValues = WorksheetFunction.Transpose(Sheet1.Range("A1:A11"))
'Here for checkboxes input
    'frm.TCSCol.CheckBoxValues = WorksheetFunction.Transpose(Sheet1.Range("B1:B11"))
    
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If frm.Executed Then
        v = frm.TCSCol.TextBoxValues
        Folha4.Range("A5:J5") = v
        
        PrintSelectRange frm.TCSCol.CheckBoxValues
    End If
End Sub

Hello,

It seems to be good.

Only missing point 2) , 3)

2)Then i need after pdf exportation, to show in message which checkbox's were printed, (show checkbox's caption) like i had earlier, the code i sent you.
(according to checkbox's selected captions)

3)Can you please remove in the code what's not being used as well?
I mean.. your develop a code, but the older things are there and don't seem to be used.

Other thing:
Shouldn't i put this?

On Error Resume Next
            Kill PATH_OP & sFileName
        On Error GoTo 0

You can check here my old code that worked great with the checkbox analysis and define ranges according to them

Private Sub PrintSelectedRanges(frm As Imprimir_checkbox, vaPrintRanges As Variant)

    Dim rSelectedRanges     As Range
    Dim vaCheckBoxes        As Variant
    Dim iCheckBoxNo         As Integer
    Dim sFileName           As String
    Dim sMessage            As String
    Dim wks                 As Worksheet

    sMessage = "As seguintes secções foram impressas:"

    vaCheckBoxes = frm.SelectedCheckBoxes

    For iCheckBoxNo = LBound(vaCheckBoxes, 1) To UBound(vaCheckBoxes, 1)

        If vaCheckBoxes(iCheckBoxNo, 1) = True Then

            If rSelectedRanges Is Nothing Then
                  Set rSelectedRanges = vaPrintRanges(iCheckBoxNo)
            Else: Set rSelectedRanges = Union(rSelectedRanges, _
                                              vaPrintRanges(iCheckBoxNo))
            End If

            If vaCheckBoxes(iCheckBoxNo, 2) <> vbNullString Then
                sMessage = sMessage & vbLf & vbLf & vbTab & vaCheckBoxes(iCheckBoxNo, 2)
            End If

        End If

    Next iCheckBoxNo

    If Not rSelectedRanges Is Nothing Then

        Set wks = vaPrintRanges(1).Parent
        sFileName = wks.Range(Celula_Nr_Entrada_OP).Value & ".pdf"

        On Error Resume Next
            Kill PATH_OP & sFileName
        On Error GoTo 0

        rSelectedRanges.ExportAsFixedFormat Type:=xlTypePDF, _
                                            Filename:=PATH_OP & sFileName, _
                                            OpenAfterPublish:=True

        MsgBox sMessage
    End If

End Sub



'=========================================================================================
'=========================================================================================


Private Function mvaPrintRanges() As Variant

    Const iNO_OF_RANGES As Integer = 11                                              '   <<< Change this value as required
    Const sRANGE_1      As String = "J1:AB75"               '-> Impressão
    Const sRANGE_2      As String = "J76:AB150"             '-> Bolha/Corte
    Const sRANGE_3      As String = "J151:AB225"            '-> Embalagem
    Const sRANGE_4      As String = "J226:AB300"            '-> Faturação
    Const sRANGE_5      As String = "J301:AB375"            '-> Máquina Envelopes Automática
    Const sRANGE_6      As String = "J376:AB450"            '-> Confeção
    Const sRANGE_7      As String = "J451:AB525"            '-> Laminagem
    Const sRANGE_8      As String = "J1:AB525"              '-> Todas
    Const sRANGE_9      As String = "J1:AB525"              '-> Todas
    Const sRANGE_10     As String = "J1:AB525"              '-> Todas
    Const sRANGE_11     As String = "J1:AB525"              '-> Todas                '   <<< Add new RANGE_n definition

    Dim vaPrintRanges   As Variant
    Dim wks             As Worksheet

    ReDim vaPrintRanges(1 To iNO_OF_RANGES)

    Set wks = ThisWorkbook.Worksheets(FOLHA_INTRODUÇÃO_OT)

    With wks
        Set vaPrintRanges(1) = .Range(sRANGE_1)
        Set vaPrintRanges(2) = .Range(sRANGE_2)
        Set vaPrintRanges(3) = .Range(sRANGE_3)
        Set vaPrintRanges(4) = .Range(sRANGE_4)
        Set vaPrintRanges(5) = .Range(sRANGE_5)
        Set vaPrintRanges(6) = .Range(sRANGE_6)
        Set vaPrintRanges(7) = .Range(sRANGE_7)
        Set vaPrintRanges(8) = .Range(sRANGE_8)
        Set vaPrintRanges(9) = .Range(sRANGE_9)
        Set vaPrintRanges(10) = .Range(sRANGE_10)
        Set vaPrintRanges(11) = .Range(sRANGE_11)                                    '   <<< Add new PrintRanges(n) definition
        
        
    End With

    mvaPrintRanges = vaPrintRanges

End Function

Can you put like that? which has the range separatly defined for each checkbox, has the message and on error kill path for not having bugs.

Hope you can help on this project

OK,OK brainzlp
Hope you could like your Christmas' gift

Hello mr simcode, thanks for your XMAS gift it's seems to be what i expect.

Just wanted to ask you 1 thing that isn't equal as my older one.

In the message box, instead of appear:
FLEXOGRAFIA , LAMINAGEM , ETC ETC

PUT:
FLEXOGRAFIA

LAMINAGEM

ETC

ETC

What i mean is that each checkbox is 1 line, with 1 in end.

Something like this probably.. although isn't working at least on me.

vbLf & vbLf & vbTab

Thanks again, and if possible just let me know how can i put this

I tried to put this:

    If Worksheets("ORDEM TRABALHO ENVELOPES").Range("A1").Value = "ADMIN" Then
    MsgBox "ADMIN"
    Else
    MsgBox "OUTRO"
    End If

In the userform_initialize, cuz i need that if "ADMIN" open it and then close when use the confirm button.
IF ELSE
Userform open but validate without any changes, if possible with displayevents false, i mean... without showing the form, just show the message.

I tried in SUB showform as well but can't put what i want. If possible let me know how can i do this

Thanks again and sorry to bother you.

I'm not clearly about what r u going to do, what result do you want. I've write several examples in the workbook.
Try it.

Originally Posted by simcode

I'm not clearly about what r u going to do, what result do you want. I've write several examples in the workbook.
Try it.

Its almost there

Why do you put 3 showforms? "Showform" , "showform1" , "showform2"



In the msgbox displaying checkbox's caption, can you put all line to left? (the checkbox's caption are starting with a TAB and then caption) - if any question let me know so i can explain better.

For my issue with "ADMIN" and "ELSE"
I noticed that Showform and showform1 open and the showform2 open and close.

I need to have something similiar according to a cell, but if possible same showform.
I will try to explain better.

This form needs will have textbox's values according to cell values ( i will put this in userform initialize).

Although if the cell a1 in sheet "ORDEM TRABALHO ENVELOPES" is different than "ADMIN" the userform must open, then click automaticly in button "Confirmar" (green one) and without even appear the userform, i mean, it's running in background, probably using displayevents = false will do the job, let me know.


If it is ADMIN in that cell, than open the userform only, then the user will edit the textbox's mannually and click manually in "confirmar" button.
If else, open in background and Confirm, printing the default ranges i put in userform_initialize and showing message.

Thanks again for your fantastic help, it's just this small things and i'm done :D

I've said, they are examples, SHOWFORM, SHOWFORM1 & SHOWFORM2 are examples, it seemd that u haven't noticed it, as u always did.

A minor change in code can fulfil the task on your msgbox, I think u can do it by youself.

Showform2 is same with your so called "open in background and confirm", don't you think so?

Number "7", It can be ignored
replace that part of code with

    For i = LBound(v) To UBound(v)
        If v(i) Then
            Set rng = Union(rng, r(i))
        End If
    Next i

textbox12 and checkbox12 not working, check Initialize of userform, there must be a loop from 1 to 11, change 11 to 12, it will work.

Originally Posted by simcode

Number "7", It can be ignored
replace that part of code with

    For i = LBound(v) To UBound(v)
        If v(i) Then
            Set rng = Union(rng, r(i))
        End If
    Next i

textbox12 and checkbox12 not working, check Initialize of userform, there must be a loop from 1 to 11, change 11 to 12, it will work.

Introducing that code with an if is necessary? Isnt doing much sense for me cuz there is no = or test being made.

About the loop, cant that be automatic using count checkbox or till last checkbox number?

Get back to me asap

if one or some of checkboxes was not selected, corresponding v(i) will be FALSE.
about the loop, will be tomorrow's topic, for i have to go sleep.

Originally Posted by simcode

if one or some of checkboxes was not selected, corresponding v(i) will be FALSE.
about the loop, will be tomorrow's topic, for i have to go sleep.

Ok i will put the code that you said. I am waiting for your help tomorrow about count checkboxs on userform inicialize

Hello simcode.

I've tested the file, but i found some problems :

    For i = LBound(v) To UBound(v)
        If v(i) Then
            Set rng = Union(rng, r(i))
        End If
    Next i

This code is giving me error on "Set rng = Union(rng, r(i))"

As soon as you can, give me a hint on how to solve that, and also the count last checkbox number at Userform_Initialize.

With that the same variable can be put in code above?

Private Function mvaPrintRanges() As Variant
    Const iNO_OF_RANGES As Integer = 12

Thanks for all the help

Check file in attach

Here it is.

Originally Posted by simcode

Here it is.

Hello simcode,

It works great can you please answer me how does the code works?

Private Function CountTxtBox() As Integer
    Dim ctrl As Control
    
    For Each ctrl In Me.Controls
        CountTxtBox = IIf(TypeName(ctrl) = "TextBox", CountTxtBox + 1, CountTxtBox)
    Next ctrl
End Function

Why counttxtbox +1 , counttxtbox ?

Thanks for all the help you gave me. It seems to be everything running, will test better tomorrow. Just explain me why sum 1 in counttxtbox as soon as you can

It counts the number of TEXTBOX of Controls. If the type of control is TEXTBOX, the number increased 1.
Mark the thread SOLVED pls.

Ok, it seems to be working, that's all that matters.

Thanks for your precious help.

Have a great new year

Hello simcode,

Can you let me know if there is a easy way to display the msgbox according the values of the textbox instead of the number of the textbox?

I want the msgbox to display from the smaller to higher value the respetive caption

If it is difficult just let me know and i will try to do differently...

How about make make the captions from smaller to higher according to the number of the textbox? It's the easiest way.

Originally Posted by simcode

How about make make the captions from smaller to higher according to the number of the textbox? It's the easiest way.

That was my second option, although by changing that i need to change all my vba code for the standard input values of textbox, which is really hard.

And there are some cases that the checkboxs cant be lined up, the are like checkbox1 true then 7 then 3 by this order, thats why i would like the message to be different cuz there are different cases according to one cell value

Let me know if we can do that message display change. (only message) thr rest is the same (the transpose of the textboxs valuea for the other sheet stills the same).

Thanks once again for your help

For example?
I'm sorry, I'm not clear about you desire, so upload a book, maybe a picture pls.

What i need is the same workbook.

But display the msgbox of the textbox's values not empty, by order of value input.

for example:
textbox1.value = 1º
textbox2.value = 3º
textbox5.value = 2º

Instead of displaying the msgbox as checkbox1.caption, then 2 then 5.

Display by number order, which means, Checkbox1.caption, then 5, then 2.