Validation built into input boxes

Question

I would be really grateful if someone could help me out with this because i am very inexperienced with macros!

I currently have this:

Hide operations from user
Application.ScreenUpdating = False
    Rows("23:23").Select
    Selection.Insert Shift:=xlDown
    Range("B23:F23").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = 2
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = 2
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = 2
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = 2
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = 2
    End With
    Range("B23").Select
    ActiveCell = Application.InputBox("Enter Registration Plate")
    If Range("B23") = False Then ActiveCell.ClearContents
Range("C23").Select
    ActiveCell = Application.InputBox("Enter Car Make")
    If Range("C23") = False Then ActiveCell.ClearContents
Range("D23").Select
    ActiveCell = Application.InputBox("Enter Car Model")
    If Range("D23") = False Then ActiveCell.ClearContents
    Range("E23").Select
    ActiveCell = Application.InputBox("Enter Car Colour")
    If Range("E23") = False Then ActiveCell.ClearContents
Range("F23").Select
    ActiveCell = Application.InputBox("Enter Car Price (###,###)")
    If Range("F23") = False Then ActiveCell.ClearContents


What i would like to happen is that if the number the user enters for the registration plate has less than 2 digits or more than 7 then the macro would display an error message and delete the row that has been created for the new car (the one i created at the start of the macro).  I understand that this would incorporate an errorhandler thing in the macro and im not sure how to do that or this particular kind of validation.  I would also need to use this kind of validation for all the other input boxes aswell as the registration box.  e.g for car make between 2 and 15 letters.

Thanks in advance!

Ed

P.s may not be able to check until tomorrow for any replies

Answer 1

Ed,

try this

Sub inputboxvalcheck()
    ActiveCell.Value = "test"
    Dim s As String: s = Application.InputBox("Enter Registration Plate")
    Dim inputlength As Integer: inputlength = Len(s)
    If inputlength < 2 Or inputlength > 7 Then
        MsgBox "Plate must be 2 to 7 letters long"
        Range("A1").EntireRow.Delete
        'ActiveCell.EntireRow.Delete
    End If
End Sub


you can take the parts you need from the code to make it work. if you try the sample as it is you will see how it works.  basically it just sets the activecell value as "test" and then shows the inputbox.  if the user input is not between 2 to 7 characters then it shows the message and deletes row 1 based on the cell A1.  all that part is for is to get the entire row and the delete method of the cell you want to delete the row for.  so you can change the A1 in the entirerow.delete line to whatever cell you want.

hope this gets you set

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 2

Ed,

try this

Sub inputboxvalcheck()
    ActiveCell.Value = "test"
    Dim s As String: s = Application.InputBox("Enter Registration Plate")
    Dim inputlength As Integer: inputlength = Len(s)
    If inputlength < 2 Or inputlength > 7 Then
        MsgBox "Plate must be 2 to 7 letters long"
        Range("A1").EntireRow.Delete
        'ActiveCell.EntireRow.Delete
    End If
End Sub


you can take the parts you need from the code to make it work. if you try the sample as it is you will see how it works.  basically it just sets the activecell value as "test" and then shows the inputbox.  if the user input is not between 2 to 7 characters then it shows the message and deletes row 1 based on the cell A1.  all that part is for is to get the entire row and the delete method of the cell you want to delete the row for.  so you can change the A1 in the entirerow.delete line to whatever cell you want.

hope this gets you set

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Thanks very much for your help!

This is almost perfect except it doesnt write the value into the active cell that I type into the input box, I want to check as I go along entering into the input box and if it is allowed by the validation I want it to type the registration plate into the right cell.  If it is incorrect then I would like it to continue with what the macro above does because that is exactly what i want! So you have already helped me out a great deal and I would be grateful if you could just help me sort out this slight problem

Hope you understand what I mean!

Thanks

Ed

Answer 3

Ed,

i see what you want to do and it can be done in your code.  i would suggest possibly using a few subs (macros) to call each one after another if you want to continue on this way.  the first one would check the plate, second check make, third check model, etc...  if they as they input each part you can check and store it if correct.  if not then you notify them if they entered an incorret value at each individual check.

but what i am thinking is you are looking at accomplishing this task the same way i would have when i first started playing with marcos.  it was very manuall and the macro recorded teaches you to do things how you would manaully do it by hand.  the problem with it is that there are a lot of steps that performed when recording a macro that are not necessary.  and they might teach you to do things out of order. 

for example if you want to add a row with information about the car:  you might add a new row then you ask for the values, put them in if they are correct and if not then delete the row.

adding a row before you need it is not necessary.  the idea would be to fill out your information first and if all correct then add the new row with the information.  a macro won't really show you how to do that.

so what i suggest based on what i think you want is to try a userform instead.  a userform will allow you to hanlde all the information for the car at one time and using events from controls such as textboxes you can control the user input.  and once they have entered in all the input and is correct, you can add the new row.  this setup can really make it easy for you to do complex handling of the information in an easy and controlled way.

what do you think about trying the userform?

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 4

Ed,

i see what you want to do and it can be done in your code.  i would suggest possibly using a few subs (macros) to call each one after another if you want to continue on this way.  the first one would check the plate, second check make, third check model, etc...  if they as they input each part you can check and store it if correct.  if not then you notify them if they entered an incorret value at each individual check.

but what i am thinking is you are looking at accomplishing this task the same way i would have when i first started playing with marcos.  it was very manuall and the macro recorded teaches you to do things how you would manaully do it by hand.  the problem with it is that there are a lot of steps that performed when recording a macro that are not necessary.  and they might teach you to do things out of order. 

for example if you want to add a row with information about the car:  you might add a new row then you ask for the values, put them in if they are correct and if not then delete the row.

adding a row before you need it is not necessary.  the idea would be to fill out your information first and if all correct then add the new row with the information.  a macro won't really show you how to do that.

so what i suggest based on what i think you want is to try a userform instead.  a userform will allow you to hanlde all the information for the car at one time and using events from controls such as textboxes you can control the user input.  and once they have entered in all the input and is correct, you can add the new row.  this setup can really make it easy for you to do complex handling of the information in an easy and controlled way.

what do you think about trying the userform?

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

yeah that would be great

I have never done a userform so you would have to show me how to do that if you could

Thanks

Ed

Answer 5

Ok,

do this:

1. open your workbook in excel
2. hit  ctrl + F11 keys together to open the visual basic editor
3. insert menu - click userform
4. click and drag textboxes and labels for each individual car information item, and add 2 buttons: 1 for an "ok" button and 1 for a "cancel" button.

After you have it designed how you want
double click the button to bring up the code window.  this will also write the click event sub for you.  inside this sub you can code the checks you want for the textbox entries and if they all match what you want then you can add the row to the sheet.

say you add a textbox on the form.  it's default id or name will be Textbox1.  you can refer to it's text property to get the user input like this.  textbox1.text.  you can do some checks like this:
if textbox1.text.length < 2 or textbox1.text.length > 7 then
  msgbox"Plate must be between 2 and 7 characters long"
end if

or

if textbox1.text <> "Chevy" then
  msgbox "must be Chevy"
end if

<>   means not equal



you can access a worksheet in code like this:
currently selected row  -  activesheet
a specific sheet - sheet1, sheet2, etc...


here is an example to insert a row and populate some columns in the new row

Sub insertnewrecord()
Selection.EntireRow.Insert
Dim currentrow As Integer: currentrow = ActiveCell.Row
Dim sht As Worksheet: Set sht = Sheet1
sht.Cells(currentrow, 1).Value = "Plate value"
sht.Cells(currentrow, 2).Value = "Make value"
sht.Cells(currentrow, 3).Value = "Model value"
End Sub

you can use this code in the button click event without the sub and end sub lines. 
or you can call this sub from the button click event like this:
call insertnewrecord
that will execute the code in the sub when you click the button on the form

instead of the "plate value" etc...  you can refer to the textbox text properties for each textbox
sht.cells(currentrow, 1).value = textbox1.text

you can also rename your textboxes in the properties.  you will see those options on the left side of the visual basic editor.  helps to make the names refer to what their use it.  txtRegistration   for example    or    txtboxMake,   etc... 

to show the userform from the worksheet, you can use the view menu - toolbars - control toolbox to bring up the toolbox menu
drag a command button to the sheet
right click on it and select view code
this will bring up the click event
in the event sub use the name of the userform (userform1 by default) along with the show method   -   userform1.show

in the worksheet you can click the option in the toolbox to turn off design view
then click on the button and the userform will load



to give you some more info on how to work with code:
above i declared a new worksheet object which refers to sheet1 using dim sht as worksheet: set sht = sheet1
that is actually two lines that are combined by seperating them with a colon. 
they can also be written like this
dim sht as worksheet
set sht = sheet1

everything you work with in vb such as button, textbox, worksheet, etc... are all objects.  they are just different classes of objects. each class has it's own properties such as rows, column and cells for a worksheet, or text, backcolor, etc... for buttons, textboxes, etc...  and each class has it's own methods which are called subroutines and functions.  they also have event subroutines which are called or "raised" bu user interaction wth them such as clicking or changing text, etc...

you can refer to an object by name such as sheet1 as well.  i declared a new object to refer to it by a name of sht.  you don't have to do that but you can also refer to objects in a more general way and declaring them specifically can help give you intellisense options which is the dropdown list that pops up as you type code to show you the methods and properties of an object.  it would be best if you can type out the code even when you can copy and paste it so you can see how intellisense works and let it help you.  the methods and properties can give you clues how to do things.



you will have more questions after this but this will get you started.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 6

Ed,

just wanted to ask if you you don't mind

i am setting up a new section on my website for allowing members to upload projects so they can get help on them. 
there are file sharing sites out there but i don't trust them anymore since having some issues after downloading from a few a while back.  some might be a front for maliscious use i think.  but aside from that many forum members ask about emailing project to me and others, i figure this option would be good to have one upload for more to download and help rather than have to email to multiple people or just only one person.

is this something that you think you would be interested in using?  my site is mainly for vb.net but i am integrating some new features for vba as well for office users.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 7

Ok,

do this:

1. open your workbook in excel
2. hit  ctrl + F11 keys together to open the visual basic editor
3. insert menu - click userform
4. click and drag textboxes and labels for each individual car information item, and add 2 buttons: 1 for an "ok" button and 1 for a "cancel" button.

After you have it designed how you want
double click the button to bring up the code window.  this will also write the click event sub for you.  inside this sub you can code the checks you want for the textbox entries and if they all match what you want then you can add the row to the sheet.

say you add a textbox on the form.  it's default id or name will be Textbox1.  you can refer to it's text property to get the user input like this.  textbox1.text.  you can do some checks like this:
if textbox1.text.length < 2 or textbox1.text.length > 7 then
  msgbox"Plate must be between 2 and 7 characters long"
end if

or

if textbox1.text <> "Chevy" then
  msgbox "must be Chevy"
end if

<>   means not equal



you can access a worksheet in code like this:
currently selected row  -  activesheet
a specific sheet - sheet1, sheet2, etc...


here is an example to insert a row and populate some columns in the new row

Sub insertnewrecord()
Selection.EntireRow.Insert
Dim currentrow As Integer: currentrow = ActiveCell.Row
Dim sht As Worksheet: Set sht = Sheet1
sht.Cells(currentrow, 1).Value = "Plate value"
sht.Cells(currentrow, 2).Value = "Make value"
sht.Cells(currentrow, 3).Value = "Model value"
End Sub

you can use this code in the button click event without the sub and end sub lines. 
or you can call this sub from the button click event like this:
call insertnewrecord
that will execute the code in the sub when you click the button on the form

instead of the "plate value" etc...  you can refer to the textbox text properties for each textbox
sht.cells(currentrow, 1).value = textbox1.text

you can also rename your textboxes in the properties.  you will see those options on the left side of the visual basic editor.  helps to make the names refer to what their use it.  txtRegistration   for example    or    txtboxMake,   etc... 

to show the userform from the worksheet, you can use the view menu - toolbars - control toolbox to bring up the toolbox menu
drag a command button to the sheet
right click on it and select view code
this will bring up the click event
in the event sub use the name of the userform (userform1 by default) along with the show method   -   userform1.show

in the worksheet you can click the option in the toolbox to turn off design view
then click on the button and the userform will load



to give you some more info on how to work with code:
above i declared a new worksheet object which refers to sheet1 using dim sht as worksheet: set sht = sheet1
that is actually two lines that are combined by seperating them with a colon. 
they can also be written like this
dim sht as worksheet
set sht = sheet1

everything you work with in vb such as button, textbox, worksheet, etc... are all objects.  they are just different classes of objects. each class has it's own properties such as rows, column and cells for a worksheet, or text, backcolor, etc... for buttons, textboxes, etc...  and each class has it's own methods which are called subroutines and functions.  they also have event subroutines which are called or "raised" bu user interaction wth them such as clicking or changing text, etc...

you can refer to an object by name such as sheet1 as well.  i declared a new object to refer to it by a name of sht.  you don't have to do that but you can also refer to objects in a more general way and declaring them specifically can help give you intellisense options which is the dropdown list that pops up as you type code to show you the methods and properties of an object.  it would be best if you can type out the code even when you can copy and paste it so you can see how intellisense works and let it help you.  the methods and properties can give you clues how to do things.



you will have more questions after this but this will get you started.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Thanks again for your help

when i double click the button i get this:

Private Sub CommandButton10_Click()

End Sub

Private Sub CommandButton1_Click()

End Sub

Private Sub Label2_Click()

End Sub



Which one would i write the code under because as i understand each one is a different part of macro?

Which part of the code you have mentioned would i write underneath each one of these

As you know I am not very good with coding so try to keep it simple ;)

Thanks

Ed

Answer 8

Ok,

do this:

1. open your workbook in excel
2. hit  ctrl + F11 keys together to open the visual basic editor
3. insert menu - click userform
4. click and drag textboxes and labels for each individual car information item, and add 2 buttons: 1 for an "ok" button and 1 for a "cancel" button.

After you have it designed how you want
double click the button to bring up the code window.  this will also write the click event sub for you.  inside this sub you can code the checks you want for the textbox entries and if they all match what you want then you can add the row to the sheet.

say you add a textbox on the form.  it's default id or name will be Textbox1.  you can refer to it's text property to get the user input like this.  textbox1.text.  you can do some checks like this:
if textbox1.text.length < 2 or textbox1.text.length > 7 then
  msgbox"Plate must be between 2 and 7 characters long"
end if

or

if textbox1.text <> "Chevy" then
  msgbox "must be Chevy"
end if

<>   means not equal



you can access a worksheet in code like this:
currently selected row  -  activesheet
a specific sheet - sheet1, sheet2, etc...


here is an example to insert a row and populate some columns in the new row

Sub insertnewrecord()
Selection.EntireRow.Insert
Dim currentrow As Integer: currentrow = ActiveCell.Row
Dim sht As Worksheet: Set sht = Sheet1
sht.Cells(currentrow, 1).Value = "Plate value"
sht.Cells(currentrow, 2).Value = "Make value"
sht.Cells(currentrow, 3).Value = "Model value"
End Sub

you can use this code in the button click event without the sub and end sub lines. 
or you can call this sub from the button click event like this:
call insertnewrecord
that will execute the code in the sub when you click the button on the form

instead of the "plate value" etc...  you can refer to the textbox text properties for each textbox
sht.cells(currentrow, 1).value = textbox1.text

you can also rename your textboxes in the properties.  you will see those options on the left side of the visual basic editor.  helps to make the names refer to what their use it.  txtRegistration   for example    or    txtboxMake,   etc... 

to show the userform from the worksheet, you can use the view menu - toolbars - control toolbox to bring up the toolbox menu
drag a command button to the sheet
right click on it and select view code
this will bring up the click event
in the event sub use the name of the userform (userform1 by default) along with the show method   -   userform1.show

in the worksheet you can click the option in the toolbox to turn off design view
then click on the button and the userform will load



to give you some more info on how to work with code:
above i declared a new worksheet object which refers to sheet1 using dim sht as worksheet: set sht = sheet1
that is actually two lines that are combined by seperating them with a colon. 
they can also be written like this
dim sht as worksheet
set sht = sheet1

everything you work with in vb such as button, textbox, worksheet, etc... are all objects.  they are just different classes of objects. each class has it's own properties such as rows, column and cells for a worksheet, or text, backcolor, etc... for buttons, textboxes, etc...  and each class has it's own methods which are called subroutines and functions.  they also have event subroutines which are called or "raised" bu user interaction wth them such as clicking or changing text, etc...

you can refer to an object by name such as sheet1 as well.  i declared a new object to refer to it by a name of sht.  you don't have to do that but you can also refer to objects in a more general way and declaring them specifically can help give you intellisense options which is the dropdown list that pops up as you type code to show you the methods and properties of an object.  it would be best if you can type out the code even when you can copy and paste it so you can see how intellisense works and let it help you.  the methods and properties can give you clues how to do things.



you will have more questions after this but this will get you started.

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Thanks again for your help

when i double click the button i get this:

Private Sub CommandButton10_Click()

End Sub

Private Sub CommandButton1_Click()

End Sub

Private Sub Label2_Click()

End Sub



Which one would i write the code under because as i understand each one is a different part of macro?

Which part of the code you have mentioned would i write underneath each one of these

As you know I am not very good with coding so try to keep it simple ;)

Thanks

Ed

Ahh i see now

Each one of those parts is the code for what happens after each button is pressed

Ok then how do write the macro for the cancel button?

Also how do I get this user form to show after clicking a button on my sheet

Answer 9

Just to bring you further up to date this is what i currently have

the code on the form:

Private Sub CommandButton10_Click()

End Sub

Private Sub CommandButton_Click()
If TextBox1.Text.Length < 3 Or TextBox1.Text.Length > 15 Then
  MsgBox "Car Make must be between 3 and 15 characters long"
End Sub

Private Sub CommandButton1_Click()
If TextBox1.Text.Length < 2 Or TextBox1.Text.Length > 7 Then
  MsgBox "Registration Plates must be between 2 and 7 characters long"
End If
End Sub

Private Sub CommandButton2_Click()

End Sub

Private Sub CommandButton4_Click()
If TextBox1.Text.Length < 5 Or TextBox1.Text.Length > 8 Then
  MsgBox "Price must be more than 1,000 and less than 1,000,000"
End Sub

Private Sub CommandButton6_Click()
If TextBox1.Text.Length < 3 Or TextBox1.Text.Length > 10 Then
  MsgBox "Colour must be between 3 and 10 characters long"
End Sub

Private Sub CommandButton8_Click()
If TextBox1.Text.Length < 1 Or TextBox1.Text.Length > 20 Then
  MsgBox "Car Model must be between 1 and 20 characters long"
End Sub

Private Sub Label2_Click()

End Sub

Private Sub UserForm_Click()

End Sub






The code on the command button:

Private Sub CommandButton1_Click()

CarDataEntryForm.Show
Selection.EntireRow.Insert
Dim currentrow As Integer: currentrow = ActiveCell.Row
Dim sht As Worksheet: Set sht = Carlist
sht.Cells(currentrow, 1).Value = "textbox1.text"
sht.Cells(currentrow, 2).Value = "textbox2.text"
sht.Cells(currentrow, 3).Value = "textbox3.text"
sht.Cells(currentrow, 4).Value = "textbox4.text"
sht.Cells(currentrow, 5).Value = "textbox5.text"

End Sub





When i click the command button and enter information and click ok the error message comes up saying Compile error, Invalid qualifier and highlights this part of the code for the buttons

If TextBox1.Text.Length





Any help would be appreciated!

Thanks

Ed

Answer 10

i wrote it incorrect.  it should be   textbox1.textlength          textlength is one word   not  text.length

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 11

thanks!

the validation is now working

Now it just wont transfer the info to the table, should it transfer it when the X on the form is clicked or when each individual OK is pressed after the validation is checked?

When i press the X now it debugs and highlights this part of the code:


Dim sht As Worksheet: Set sht = Carlist



Also how would i program the Cancel buttons to delete the text in the text boxes on the forms would it just be textbox1.textdelete or something like that?

Thanks

Ed

Answer 12

Ed,

i don't get alerts for all posts in threads so i missed some of the others you posted yesterday.  the ones about the click subs for the buttons. 


the transfer:
usually you won't find buttons for each user input (textbox).  you would have 1 button which will commit all the input where you want it to go.  so in your case you coulld remove all but one button and move the code for each textbox to the click event of that one button.  or you can also take advantage of other events for the textboxes such as the exit event. 

example
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  If Me.TextBox1.TextLength = 0 Then
  MsgBox "enter text"
  End If
End Sub

when the cursor leaves the textbox the event above is raised and will execute the code



if you validate all the boxes in the click event of the ok button then you will need to be sure that everything is correct before allowing the new row to be created.  you can do this by checking the boxes in order and if one doesn't meet your validation then you can use exit sub to stop the code from being executed past that point
example

i'm just goint to type this out as an example, it's not exact for the click event
okbutton click event sub
if me.textbox1.textlength = 0 then
  msgbox "enter text in textobx 1"
  exit sub
end if

if me.textbox2.textlength = 0 then
  msgbox "enter text in textobx 2"
  exit sub
end if
end click event sub


before i go any further,   "me" in the code i posted refers to the form itself.  you can remove that if needed.


so the basic idea with all of this is to keep things how a user normally interacts with an entry form.  they have an ok button to accept their input and a cancel button or close button to cancel the input from being commited.  of course it does not have to be that way.  it can be however you want, you just need to be sure you let the user know how it works or atleast make it self explanatory.  it's your application so you make it work however you want. 


for the sheet line showing up in debug:
those sheet names i believe are based on the sheet order.  so sheet1 would be the first sheet even if you renamed it.  if you want to use the sheet name then try this:
Set sht = Worksheets("Carlist")

that way you can refer to the sheet by name if it makes it easier for you



something else that will help you with understanding how the code works:
if you move your code from the other button click events into the ok button click event, on the right side of the code editor there is a grey bar down the side in between the code and the explorer on the left where your list of objects and properties are.  you can click inside of that bar next to a line of code and it will put a red dot and hightlight the line in red.  this is called a breakpoint.  when the code is executed it will stop on that breakpoint and bring up the code window with that line highlighted in yellow.  after that you can hit the F8 key to step through the code one line at at time.  so if you set a breakpoint at the first line in your event sub you will be able to step through each line and see how they are executed in order.  after you step past a line you an also hover your cursor over the objects in previouse line to see their values.

example
some sub
dim s as string
s = "hi"
end sub

if you set a breakpoint at the last line: end sub     run the code and once it hits the end sub line hover your mouse over the s in the dim s as string line.  this will show you the value of the variable we declared as "s".  you can also hover over the s in   s = "hi".  anywhere you have that variable you can hover over it to see it's value.

you can also set a breakpoint at the first line and run.  after it hits, hover your mouse over s.  you won't see anything because the value has not been set.  hit F8 to move to s = "hi", it will still not show anything if you hover.  hit F8 again, now it will have executed and moved past   s = "hi"  which will have set the value to "hi". 

right now that may not seem to be very useful because you are not doing anything complex yet but it will help later as you add more to it.  it can help you determine what is happening in your code.  you can hover over any objects you have to find out more about them at any point in stepping through.


also, keep in mind during all this that i usually work with vb.net so i may write out things or tell you things that would normally pertain to vb.net and not vba.  it's awkward to come back to vba sometimes.  also thought i would mention to you, depending on your purpose for all this, you might look at the visual studio express editions.  they are free and are a full development software to create stand alone applicaiton that don't rely on excel or other applications.  you could make your own database similar to excel but just for your needs.  with visual studion you could make your own version of excel if you wanted to.  excel vba is fun and powerful but sometimes excel gets in the way.  you have to work inside excel bounds. 

just thought i would mention it.  you might like to look at it to see the difference in developing your own applicaiton versus an excel workbook

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 13

Thanks again

Ok I am nearly done now, i have moved all the codes onto one OK button.  It works fine for validation, but if all the values are correct then i want it to close its self automatically and at the moment it just does nothing when all the info is the correct length

When i click the X on the form after it is shown it writes all the Textbox1, Textbox2 etc. into all the cells rather than the info written into the userform

So i want the OK to do the same as the cross when all the info is correct, but put the info entered into the userform into the cells rather than the names of the textboxes!    Also when i press X it still creates a new row.    I would like it not to create a new row until all the information has been validated.  I want the cross to cancel the data entry not allow it!  Below are the codes i currently have



Command Button:

Private Sub CommandButton1_Click()

CarDataEntryForm.Show
Selection.EntireRow.Insert
Dim currentrow As Integer: currentrow = ActiveCell.Row
Dim sht As Worksheet: Set sht = Worksheets("Carlist")

sht.Cells(currentrow, 2).Value = "textbox1.text"
sht.Cells(currentrow, 3).Value = "textbox2.text"
sht.Cells(currentrow, 4).Value = "textbox3.text"
sht.Cells(currentrow, 5).Value = "textbox4.text"
sht.Cells(currentrow, 6).Value = "textbox5.text"

End Sub



User form Code

Private Sub CommandButton4_Click()
If TextBox1.TextLength < 2 Or TextBox1.TextLength > 7 Then
  MsgBox "Registration Plates must be between 2 and 7 characters long"
  End If
  If TextBox2.TextLength < 3 Or TextBox2.TextLength > 15 Then
  MsgBox "Car Make must be between 3 and 15 characters long"
  End If
 If TextBox3.TextLength < 1 Or TextBox3.TextLength > 20 Then
  MsgBox "Car Model must be between 1 and 20 characters long"
  End If
  If TextBox4.TextLength < 3 Or TextBox4.TextLength > 10 Then
  MsgBox "Colour must be between 3 and 10 characters long"
  End If
If TextBox5.TextLength < 5 Or TextBox5.TextLength > 8 Then
  MsgBox "Price must be more than 1,000 and less than 1,000,000"
  End If
 

I have a deadline to meet tomorrow so hopefully you can get back to me :)

Many thanks

Ed

Answer 14

Private Sub CommandButton1_Click()
CarDataEntryForm.Show
End Sub



User form Code

Private Sub CommandButton4_Click()

If TextBox1.TextLength < 2 Or TextBox1.TextLength > 7 Then
  MsgBox "Registration Plates must be between 2 and 7 characters long"
End If
  If TextBox2.TextLength < 3 Or TextBox2.TextLength > 15 Then
  MsgBox "Car Make must be between 3 and 15 characters long"
End If
If TextBox3.TextLength < 1 Or TextBox3.TextLength > 20 Then
  MsgBox "Car Model must be between 1 and 20 characters long"
End If
If TextBox4.TextLength < 3 Or TextBox4.TextLength > 10 Then
  MsgBox "Colour must be between 3 and 10 characters long"
End If
If TextBox5.TextLength < 5 Or TextBox5.TextLength > 8 Then
  MsgBox "Price must be more than 1,000 and less than 1,000,000"
End If



Selection.EntireRow.Insert
Dim currentrow As Integer: currentrow = ActiveCell.Row
Dim sht As Worksheet: Set sht = Worksheets("Carlist")

sht.Cells(currentrow, 2).Value = "textbox1.text"
sht.Cells(currentrow, 3).Value = "textbox2.text"
sht.Cells(currentrow, 4).Value = "textbox3.text"
sht.Cells(currentrow, 5).Value = "textbox4.text"
sht.Cells(currentrow, 6).Value = "textbox5.text"

End Sub




you need to refer to the object itself and it's text property.  anything inside quotes is a string and will literally be taken as what is is in between the quotes.  so you are not getting the text property value of the textbox, you are getting a string value that says  textbox1.text.  look at how you referred to the textlenght to get a clue how to fix it.

the next issue you have is that even if they do not meet your validation you will still add a new row with the information.  you need to look back at what i showed before about using exit sub.

if you want to show messages for all, one after another, rather than only the first one that doesn't meet your validation then you need to incorporate a boolean which can be set to true or false.  then based on whether it is true or false then you can add the new car row.

to use a boolean you just need to declare it with a name you choose like this:
dim CarIsValid as boolean: CarIsValid = true

you can set this value to false if any of the validations are not met after each check

then you can use an if check to determine if CarIsValid = true    to allow the new row or not


to close a form use me.hide

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 15

Private Sub CommandButton1_Click()
CarDataEntryForm.Show
End Sub



User form Code

Private Sub CommandButton4_Click()

If TextBox1.TextLength < 2 Or TextBox1.TextLength > 7 Then
  MsgBox "Registration Plates must be between 2 and 7 characters long"
End If
  If TextBox2.TextLength < 3 Or TextBox2.TextLength > 15 Then
  MsgBox "Car Make must be between 3 and 15 characters long"
End If
If TextBox3.TextLength < 1 Or TextBox3.TextLength > 20 Then
  MsgBox "Car Model must be between 1 and 20 characters long"
End If
If TextBox4.TextLength < 3 Or TextBox4.TextLength > 10 Then
  MsgBox "Colour must be between 3 and 10 characters long"
End If
If TextBox5.TextLength < 5 Or TextBox5.TextLength > 8 Then
  MsgBox "Price must be more than 1,000 and less than 1,000,000"
End If



Selection.EntireRow.Insert
Dim currentrow As Integer: currentrow = ActiveCell.Row
Dim sht As Worksheet: Set sht = Worksheets("Carlist")

sht.Cells(currentrow, 2).Value = "textbox1.text"
sht.Cells(currentrow, 3).Value = "textbox2.text"
sht.Cells(currentrow, 4).Value = "textbox3.text"
sht.Cells(currentrow, 5).Value = "textbox4.text"
sht.Cells(currentrow, 6).Value = "textbox5.text"

End Sub




you need to refer to the object itself and it's text property.  anything inside quotes is a string and will literally be taken as what is is in between the quotes.  so you are not getting the text property value of the textbox, you are getting a string value that says  textbox1.text.  look at how you referred to the textlenght to get a clue how to fix it.

the next issue you have is that even if they do not meet your validation you will still add a new row with the information.  you need to look back at what i showed before about using exit sub.

if you want to show messages for all, one after another, rather than only the first one that doesn't meet your validation then you need to incorporate a boolean which can be set to true or false.  then based on whether it is true or false then you can add the new car row.

to use a boolean you just need to declare it with a name you choose like this:
dim CarIsValid as boolean: CarIsValid = true

you can set this value to false if any of the validations are not met after each check

then you can use an if check to determine if CarIsValid = true    to allow the new row or not


to close a form use me.hide

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Ok, i have found a way so that it only displays one validation message by doing this on the button code

Private Sub CommandButton4_Click()
If TextBox1.TextLength < 2 Or TextBox1.TextLength > 7 Then
  MsgBox "Registration Plates must be between 2 and 7 characters long"
  Exit Sub
  End If
  If TextBox2.TextLength < 3 Or TextBox2.TextLength > 15 Then
  MsgBox "Car Make must be between 3 and 15 characters long"
  Exit Sub
  End If
 If TextBox3.TextLength < 1 Or TextBox3.TextLength > 20 Then
  MsgBox "Car Model must be between 1 and 20 characters long"
  Exit Sub
  End If
  If TextBox4.TextLength < 3 Or TextBox4.TextLength > 10 Then
  MsgBox "Colour must be between 3 and 10 characters long"
  Exit Sub
  End If
If TextBox5.TextLength < 5 Or TextBox5.TextLength > 8 Then
  MsgBox "Price must be more than 1,000 and less than 1,000,000"
  Exit Sub
  End If
 




I tried to get the command button to refer to the form but it just refused to i tried textbox1.text, me.textbox1.text,(all without quotes) it just wouldnt work and ended up debugging every time

Any suggestions?

Answer 16

you should be able to use this

sht.Cells(currentrow, 1).Value = TextBox1.Text
same for textbox2, etc...

if you have more trouble then post all the code for the ok button including the populating the row values from the textboxes

---

FREE DEVELOPER TOOLS, CODE & PROJECTS at www.srsoft.us Database Code Generator and Tutorial

Answer 17

Thanks so much

Just managed to do it just before my deadline the other day - for two separate data entries so you really saved me there - they are both working great!

:)

Thanks again

Ed