Userform data validation

[Rodrigues]

All
I'm not sure if I can ask more than a question in one thread, but here goes (apologies if I can't).
Have 11 Comboxbox, I need to ensure at least one of combobox have data, otherwise pop up a message saying: "Select one of the options".

Second question is, it's possible to format a textbox in two lines so when a user to key in a long text it splits in two lines make the text visible?
Thanks in advance.
R

 

[Chihiro]

Assuming you don't care what type of data is in each textbox (excluding when it's just space char),

Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim iStr As String: iStr = ""

For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ComboBox" Then
        iStr = iStr & Trim(ctrl.Value)
    End If
Next ctrl

If Len(iStr) = 0 Then
    MsgBox "Please input data in at least one Combobox"
    ComboBox1.SetFocus
    Exit Sub
Else
'Do something if there's at least one textbox with data input'
End If

End Sub

For 2nd question, set Multiline property of TextBox to True. Adjust font size and Textbox size as needed.

Edit: Woops, I missed that it was Combobox you wanted to check. Just replace "TextBox" with "ComboBox". Code updated.

 

[Rodrigues]

Hi Chihiro
I'm sorry to be a pain, haven't explained properly, I have multiple combobox and text box on the userform, which I'm checking already when I click on cmdAdd button, what I would like to do now is that, check as well (at least one must have data) a number of combox (comboBox 8, 9, 10, 11, 12, 13,14, 15, 16,17,18), try to amend the code without luck, could you please help me?
Hope it makes sense.
Thanks
R

 

[Chihiro]

Upload sample file with some examples and what should happen.

 

[Rodrigues]

Will do one and upload.
Thanks

 

[Rodrigues]

Apologies for delay.
File attached, the combox needed to be checked (at least one have to have data) highlighted at yellow.

Not abusing of your good will, it's possible to attach the pdf (after being saved) and send it by email, have some code for the email already on this file but emails the full list.
All the forms will be saved numeric order I.e.: 1, 2, 3,4, etc...
Thanks again for your help.
Regards
R

 

[Chihiro]

Here's the code for validation.

Dim nameStr As String, iStr As String

For i = 8 To 18
    nameStr = "ComboBox" & i
    iStr = iStr & Trim(Me.Controls(nameStr).Value)
Next

If Len(iStr) = 0 Then
    MsgBox "Please input data in at least one Combobox", vbCritical
    Exit Sub
End If

For the email do you mean that you want to send email of the Userform converted to PDF? Or is it just single line item after submission?

 

[Rodrigues]

Chihiro
thanks for this, exactly what I was looking for, excellent.
Regarding the email, would like to send email of the userform converted to pdf.
Thanks a million.

 

[Chihiro]

Looking at your code. You don't have any routine to export Userform as PDF. Only the precursor ".PrintForm" you need further setting to print as PDF and in landscape orientation.

And since it's pain to change default printer setting (at least for me). Use following code instead (Teeroy's code in link).

http://www.mrexcel.com/forum/excel-questions/737916-visual-basic-applications-save-userform-pdf.html

For your purpose, I added ".PageSetup" for the sheet.

Full snippet below.

Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Const VK_SNAPSHOT = 44
Const VK_LMENU = 164
Const KEYEVENTF_KEYUP = 2
Const KEYEVENTF_EXTENDEDKEY = 1

Private Sub CommandButton3_Click()

    Dim pdfName As String
    Dim newWS As Worksheet
  
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
  
    DoEvents 'Otherwise, all of screen would be pasted as if PrtScn rather than Alt+PrtScn was used for the copy.
  
    Set newWS = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
    newWS.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False
    With newWS.PageSetup
        .Orientation = xlLandscape
        .PrintArea = "$A$1:$R$36"
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    pdfName = ActiveWorkbook.Path & "\" & Me.Name & " " & Format(Now, "yyyy-mmm-dd hmm") & ".pdf"
    newWS.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=pdfName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Application.DisplayAlerts = False
    newWS.Delete
    Application.DisplayAlerts = True
End Sub

I just got busy at work. Will update with email code later.

 

[Chihiro]

Here you go.

Just as note. I've set it to ".Display" the email for testing purpose. Just change it to ".Send" if you want to send it without reviewing the email.

 

[Rodrigues]

Hi Chihiro
I can't thank you enough for this.
I'm wondering if you could help me with a couple things, changed on module 3 to
.PaperSize = xlPaperA4 , as I use A4 sheet, but when print the document the form is printed in the middle of the page, can this be fitted to print on A4, tried to play with code without success (got errors), also could we add the document number "Ref" into the file name.
Thank very much again

 

[Chihiro]

Here see attached.

 

[Rodrigues]

Tanks a lot.
R

 

[Rodrigues]

Chihiro
Apologies to come back to this one, now have finished the userform with all data needed and sometimes (not always) pop up the following error:
Run-time error '1004':Method 'PastedSpecial' of object'_Worksheet'failed
C

newWS.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False

ould you please help me with this one?
attached is the code highlighted when select debug.
Thanks again
R