creating a "error checking" command button

idakuna · May 21st, 2002, 09:11 PM

OK I am going to try to describe this as best I can...
I have information that users enter in column B. If information is entered in any cell of column B then other information must be entered in the corresponding rows of columns C through G. I want the users to verify that this is happening by clicking a command button that launches a click event. I know I will need to use For...Next statements but I need help! Maybe using a Do...Loop statement? Any examples to help get my brain going would be greatly appreciated. Thanks! (I will have MANY MANY more posts regarding this project over the next several weeks so bear with me!)

idakuna · May 21st, 2002, 09:19 PM

I have but I still can't figure out how to use that to accomplish what I need either. Any ideas for validations?

RET79 · May 21st, 2002, 09:27 PM

OK, I will look into using validation for this problem. I'm not sure if it can be done but will give it a good bash.

RET79

Ivan F Moala · May 21st, 2002, 09:32 PM

Quote:

On 2002-05-21 20:19, idakuna wrote:
I have but I still can't figure out how to use that to accomplish what I need either. Any ideas for validations?

Have you considered using Sheet Event
procedures ??
The one I have in mind is the
Private Sub Worksheet_Change(ByVal Target As Range)

This event code fires when cells on the worksheet are changed by the user.
The Target being the Active cell changed.

So what you would setup is A "Monitored"
range and take some action when the "Monitored"
range IS Changed.

Have a look in the Online help available
via F1 in while in the VBA Editor.
This is your 1st stop for programing examples.....next stop is to ask @ this board OR any other Board you fell confortable with.

HTH

RET79 · May 21st, 2002, 09:33 PM

Try this as a starting point:

Click D4, to go to data->validation then chooose -

Allow: Decimal
Data: Equal to
Value: =IF($C4<>"",D4,"")

Make sure the ignore blanks checkbox is unchecked.

Note: you could copy this validation throughout your range in cols D to G if you so wished, copy D4 -> select the range you wish to paste it onto -> paste special -> validation.

Then try this out.

Put whole number in C4, then whole number in D4, it should be fine.

Then, delete the number in C4 and put a whole number in D4. An error message should appear.

Hope this helps,

RET79

[ This Message was edited by: RET79 on 2002-05-21 20:45 ]

RET79 · May 21st, 2002, 09:37 PM

One obvious weakness with what I just suggested is this:

If you have values in both C4 and D4 and you deleted C4, then no error message will appear. The error message will only appear if you have typed in data in D4 while C4 ie already empty.

So I guess it would be considered an appropriate solution only if the data were to be entered through the columns in order without any going back to delete the value in the C column.

Anyway, it might give you some ideas nonetheless.

RET79

[ This Message was edited by: RET79 on 2002-05-21 20:42 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 09:11 PM	#1
idakuna New Member Join Date: May 2002 Location: Idaho Posts: 4	OK I am going to try to describe this as best I can... I have information that users enter in column B. If information is entered in any cell of column B then other information must be entered in the corresponding rows of columns C through G. I want the users to verify that this is happening by clicking a command button that launches a click event. I know I will need to use For...Next statements but I need help! Maybe using a Do...Loop statement? Any examples to help get my brain going would be greatly appreciated. Thanks! (I will have MANY MANY more posts regarding this project over the next several weeks so bear with me!)

May 21st, 2002, 09:19 PM	#2
idakuna New Member Join Date: May 2002 Location: Idaho Posts: 4	I have but I still can't figure out how to use that to accomplish what I need either. Any ideas for validations?

May 21st, 2002, 09:27 PM	#3
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	OK, I will look into using validation for this problem. I'm not sure if it can be done but will give it a good bash. RET79

May 21st, 2002, 09:33 PM	#5
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Try this as a starting point: Click D4, to go to data->validation then chooose - Allow: Decimal Data: Equal to Value: =IF($C4<>"",D4,"") Make sure the ignore blanks checkbox is unchecked. Note: you could copy this validation throughout your range in cols D to G if you so wished, copy D4 -> select the range you wish to paste it onto -> paste special -> validation. Then try this out. Put whole number in C4, then whole number in D4, it should be fine. Then, delete the number in C4 and put a whole number in D4. An error message should appear. Hope this helps, RET79 [ This Message was edited by: RET79 on 2002-05-21 20:45 ]

May 21st, 2002, 09:37 PM	#6
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	One obvious weakness with what I just suggested is this: If you have values in both C4 and D4 and you deleted C4, then no error message will appear. The error message will only appear if you have typed in data in D4 while C4 ie already empty. So I guess it would be considered an appropriate solution only if the data were to be entered through the columns in order without any going back to delete the value in the C column. Anyway, it might give you some ideas nonetheless. RET79 [ This Message was edited by: RET79 on 2002-05-21 20:42 ]