New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Userform validation

Macros, VBA, Excel Automation, etc.

Userform validation

Postby radawa » Sun Apr 24, 2016 3:31 am

Hi!

First, sorry for my bad english!

I want to make an userform which checks if there are empty textboxes. For some textbox it tells you to fill them and for others it just notifies you that you have left them blank.


What I want to do:
1. Tells that ISBN, nimi, kirjoittaja, julkaisuvuosi can not be left blank
2. If there are other blank textboxes it asks if you want to continue anyway. If the answer is yes it will print the data to the database otherwise it gives you the opportunity to fill in the empty textboxes

Here's what I have gotten so far

Code: Select all
Private Sub CommandButton1_Click()

Dim sheet1 As Worksheet

Set sheet1 = ThisWorkbook.Sheets("Tietokanta")         

nr = sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1   



If ISBN.Value = "" Then
        MsgBox "ISBN:ää ei voi jättää tyhjäksi", vbExclamation, "Input Data"
        ISBN.SetFocus
        Exit Sub
       
    End If
   
If nimi.Value = "" Then
        MsgBox "Kirjan nimeä ei voi jättää tyhjäksi", vbExclamation, "Input Data"
        nimi.SetFocus
        Exit Sub
    End If
   
If kirjoittaja.Value = "" Then
        MsgBox "kirjoittajaa ei voi jättää tyhjäksi", vbExclamation, "Input Data"
        ISBN.SetFocus
        Exit Sub
    End If

If julkaisuvuosi.Value = "" Then
        MsgBox "julkaisuvuotta ei voi jättää tyhjäksi", vbExclamation, "Input Data"
        ISBN.SetFocus
        Exit Sub
    End If
 
 
 

sheet1.Cells(nr, 1) = ISBN.Text
sheet1.Cells(nr, 2) = nimi.Text
sheet1.Cells(nr, 3) = kirjoittaja.Text
sheet1.Cells(nr, 4) = kustantaja.Text
sheet1.Cells(nr, 5) = genre.Text
sheet1.Cells(nr, 6) = julkaisuvuosi.Text
sheet1.Cells(nr, 7) = kieli.Text
sheet1.Cells(nr, 8) = kansityyppi.Text
sheet1.Cells(nr, 9) = "lainattavissa"
sheet1.Cells(nr, 10) = Date

Unload UserForm1
   
End Sub
  • 0

radawa
Rookie
 
Posts: 1
Joined: Apr 24, 2016
Reputation: 0
Excel Version: 2016

Re: Userform validation

Postby NoSparks » Sun Apr 24, 2016 9:24 am

Maybe try something along the lines of this (untested).

For workings of the message box have a look at
https://msdn.microsoft.com/en-us/librar ... 60%29.aspx
or http://www.exceltrick.com/formulas_macros/vba-msgbox/

Code: Select all
' the other text boxes
If Me.genre.Value = "" Or Me.julkaisuvuosi.Value = "" Or Me.kieli.Value = "" Or Me.kansityyppi.Value = "" Then
    response = MsgBox("Other text boxes are blank" & vbCrLf & "Are you sure you want this?", vbExclamation + vbYesNo, "Input Data")
    If response = vbYes Then
        'just carry on
    Else
        If Me.genre = "" Then
            Me.genre.SetFocus
            Exit Sub
        ElseIf Me.julkaisuvuosi.Value = "" Then
            Me.julkaisuvuosi.SetFocus
            Exit Sub
        ElseIf Me.kieli.Value = "" Then
            Me.kieli.SetFocus
            Exit Sub
        ElseIf Me.kansityyppi.Value = "" Then
            Me.kansityyppi.SetFocus
            Exit Sub
        End If
    End If
End If
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 13 guests