[Ed-_-]

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

[Rollin_Again]

Instead of using inputboxes why don't you just create a custom entry form and put your validation there? Here is how it would work. The user would either run a macro manually to call the form or you could embed a button on your workbook that when clicked would display the userform with textboxes for each of the items you mentioned. Once the user completes the form they would click a button to transfer the info to the next row of the spreadsheet. Much more clean and user friendly over using inputboxes. Should be very easy and I can help you set it up and code it. I've attached an example in it's most simple form to give you an idea. Just open the workbook and press the "AddRecord" button to start. If you insist on keeping your current format using inputboxes you should use variables and set them equal to the inputbox values and then perform all your validation before adding any of the records to the workbook.

Regards,
Rollin

[Ed-_-]

i would prefer to keep it with input boxes, i agree that your method certainly does look neater but doing something like you have shown is way beyond me!

How would i go about using "variables" - as i said previously I am very inexperienced with macros!

Thanks for your help so far

[Zack Barresse]

Originally Posted by Ed-_-

i would prefer to keep it with input boxes...

May I ask why?

[Ed-_-]

Originally Posted by Zack Barresse

May I ask why?

I prefer to develop what i already have, and i have different things happen after i enter each piece of information (copies and pastes certain parts into another sheet). I am not sure whether the method previously described can incorporate this

[Zack Barresse]

I wouldn't say using input boxes and putting their results into a cell, then checking that cell's value is efficient, and would recommend you abandon your attempts there. I guess I could see you continuing with that path if you were out for educational purposes only, but if it's efficiency you're seeking, I suggest you look at what Rollin is offering. Quite a bit more flexible, more efficient, and overall better for you in the long run. IMHO.

[Ed-_-]

I dont mind using the method suggested as long as it doesnt bug up any other parts of my macro

Also i dont have a clue how to do it so i would have to be shown

thanks

[Zack Barresse]

With inserting a row and performing some formatting? It can be done easier than what you have, and can be incorporated into any macro. I think you're good. Rollin is very good.

[Ed-_-]

i will wait for him to reply then because i wouldnt know how to format a form like that

[Aj_old]

If I may add something, I would go for the user form option:
it will allow you easier to check the input from the user;
it will allow you to run the code only in case all the needed fields are completed and in the needed format

But even if you choose to use input boxes I would say that is better first to check all the input, and only after that to add a new row and the info to the spreadsheet.

[Rollin_Again]

Ed,

Ultimately the choice is yours on how you want to design your own project. Building and incorporating forms is very easy to do and there are plenty of people here including myself who will help you put it all together. Learning to work with forms is something you can put to use in the future and this is a perfect opportunity to learn how. Don't worry about trying to understand everything at all once, we'll help you every step of the way!! The first thing to do is to try to build your form. If you open your Excel workbook and press ALT + F11 the VB editor will open. You can then click INSERT >> USERFORM to insert a blank form into the editor (the form itself can be resized by dragging the corner in or out) Once you insert the form you will see a "Control Toolbox" appear that contains all the controls (textboxes, labels, buttons, comboboxes, etc.) Clicking the control you want to use and then position your cursor on the form. Click and hold the left mouse button and drag the cursor to create and size each of your controls. Once you've inserted a control such as a textbox you can move them around wherever you want on the form and you can also copy and paste them so that they are similar sizes. After you add each control on the form you can select it with a mouseclick and you will see a "properties" window where you can change the properties of the control such as caption, color, font, etc. Play around with the form and controls a little and then let us know whether or not you want to continue down this path. If you find this too overwhelming let us know and we'll try to develop a non-form solution for you. If you decide it's something you want to continue with we'll provide details on how to add the code and validation once you have the form put together. I can also be available via realtime chat (MSN Messenger, Yahoo Messenger, etc.) if you prefer.

Regards,
Rollin

[Ed-_-]

sorry i havent had the chance to reply to this thread

I managed to solve it by doing it the way suggested in the end - with userforms so thankyou very much for your help

Ed