VBA Data Validation

Hello Guys,

I have a sheet with calculations built in that require a user to enter data in a specific way other wise it won't calculate correctly. I did post in the General Excel Forum in regards to this and got a couple good responses, but one gave me a better idea which led me to my next question.

JeteMC suggested using

Please Login or Register  to view this content.

Now this is a great start to what I need, but I also wanted to be able to make sure the user is entering a " ' " into the cell as well. With that in mind, is there a way to do this data validation within VBA across a set range of B10:I14 ? I'm trying to idiot proof the form as much as possible to make it easier for users overall.

Originally Posted by Keebellah

I have not downloaded or looked at your file, but can you explain why you need the apostrophe?
If use a userform you can use keypress to filter the allowed characters and / or values

Keebellah,

The apostrophe is needed for the VBA code that does the calculation. It looks for it so it knows that the numbers associated with it are the ft and figures after are the inches and fraction.

Example: 72' 6 3/8 in cell B10 minus 1' 3 1/8 in cell B11 = 71' 3 1/4 in cell B12.

What I want the vba validation to do is make sure the user is entering the data into the cells correctly.

I've never done a user form before that actually worked. I've loved the idea of it, but couldn't figure out how to go about doing it. B8:I14 would be the area that requires specific formatting. The user needs to enter the first Number followed by the apostrophe so the VBA code knows thats the FT, and the rest of the numbers are either inches or fractions. I borrowed the Ft and Inches VBA code from Mr. Excel and figured out how to make it work in my form, but I've been tweaking the form further and further as more and more people use it to make it easier. Lately the major problem is people not entering the "Apostrophe" or they are adding "Dashes" making the calculations incorrect.

I appreciate you taking the time to put some examples together for me. This will help me study what you did and try and learn it so that I can implement it into my project. I have a hard time trying to learn and implement without an example of what I'm trying to do.

I looked at your code, but I'm not seeing where your telling it that when someone right clicks in a cell between B8:I9 open the user input box? Can you show me the part of the code that does this?

Originally Posted by Keebellah

Hi, check the worksheets VBA, there is Before right click.
I imagined that that wold at least be known to you since you use it with the Change and Selection Change

this is the code:

Please Login or Register  to view this content.

The red section is the actual userform trigger

Keebellah,

I found the code after I already sent the question. For some reason I had a couple other excel sheets opened at the same time and it wouldn't open your code so I couldn't figure out how you did it.

Thanks for the help, I'm going to try and use your Userform and see if I can tie the calculation code into that instead of it firing off on a cell change every time. I could then eliminate a couple other trigger events, hopefully cleaning up the code a bit. Any thoughts on doing this is appreciated.

I do have another question real quick though. It is possible to have the user form make the user enter FT in one box, then inches and fraction in another all in the same userform and still have it populate the cell like you did with the Userform the first time?

Originally Posted by Keebellah

Why not alltogether in one Userform/filed?

Wait the UserForm can have the calculations built into it? That would be cool....

Originally Posted by Keebellah

But you still haven't explained what it all does with Calculation On and Off an so.
But the userfor, you want to be able to enter ft and inches in one run?

The form is just a tool for a user to enter values and have the form calculate a final figure for them. Alot of people mess up calculating fractions. So I've been trying to make it easier for users to just enter values and have the form do the math for them, therefore eliminating some user error.

If the drop down is left to Calculations On, the form will do the calculations for them.
If left off, the user has to do all the calculations by hand. This is often where alot of user error happen.

As for "Why not alltogether in one Userform/field" I'm just trying to make it as easy as possible. I figure if someone enters the Ft in one box and the Inches & Fractions into another box, it might make it easier on the user, reducing some errors.

Originally Posted by Keebellah

Yes, But could you explain the idea?
I honestly do not understand what you're doing, what you're calculating with what.
I suggest you do that so I can 'see' what you're expecting to happen.
You got the code from someone and use it but it's a whole lot of double stuff in it an VERY unclear

Capture.PNG

If a User leaves the Calculations on.
Then enters his first value into Section "B"- Open Example: 82' 3 3/8
Then enters his physical "Ullage" measurement into Section "C" -Open Example: 10' 2 3/8
Section "D" will calculate automatically. Example Answer: 72' 1 3/8

If a User flips it, and measures by "Innage". The form would then Calculate Section "B" minus Section "D" and then calculate Section "C"

Section "E" is another area that takes user input: which then Calculates section "E" minus either Section "C" or "D" depending on the situation. This part is a little tricky only because some times you won't know if something needs to be measured one way or the other. Usually if the user enters an Ullage Value, it would take the Section "C" minus Section "E" and give the difference, but sometimes you need the difference between Section "B" minus Section "E" even though you physically took an Ullage measurement.

Hopefully that make sense?

Capture1.PNG

I've manage to murder your UserForm.

Originally Posted by Keebellah

It's bed-time here, more tomorrow.

Have a good night. Appreciate you taking the time to show me new things. I'll keep plugging away at this so I can trying a familiarize myself with it.

Hey sorry, I wasn't getting any notices that you replied to the thread, thought you gave up on me . Let me catch up and I'll answer what I can in 1 response.

Users input data into:

B8
B9
B10
B11 if Cell A5 isn't switched to INNAGE. Otherwise user enters into B12 but I guess this could be changed though so the input is always in B11. Probably would make more sense.
B13

I'm looking at your new one, What is textbox 2 for in the user input form?

Oh ok then. Do we need it?

Let me play with this and see if I can figure out how to manipulate it myself a bit. I tried resizing the box before, but it didn't seem to work correctly and was wrapping the text or overlapping it. Couldn't figure out what I was doing wrong.

Ok I'm thinking with the way you have it setup to make the user Right Click to bring up the input box, that I'm going to remove the "Calculate Difference Yes/No" Really no point in having that. If the user right clicks to bring up the box, I'd like to make it calculate once the box closes. If for some reason the user wants to manually input something, they can simple just left click in the box and enter what they want right?

Am I correct in this thinking? I understand there code looking for cell A4, but with that commented or removed, could this be done?

Ok thanks. Working through it still. Coming up with more ideas as I go along.

Thats the problem with learning new things. You come to find the way you were doing things were slow and obsolete Then you get to start all over again.

Definitely alot of fun. My problem is that I forget things very easily and takes me a while to learn things to become fluent in them. In general I can pick things up fairly quick to manipulate them, and not fully understand them. This is where I rely on help from guys like you that will take the time to point things out for me so I know what I did wrong or overlooked.

And to answer one of your questions I missed:

In relation to what I'm dealing with:
Innage = measured from the bottom of a liquid to the top of the liquid.
Ullage = measured from the top of the liquid to a fixed point above the liquid.

Example:
Fixed Height of 10ft
Top of liquid is 8ft = Ullage would be 2ft
OR an Innage of 8ft

Some cases you can only measure the air space "ULLAGE" between the height and liquid, otherwise your measuring device could be destroyed.

So I've managed to manipulate a bit of the code to do what I want, but could use some help / suggestions.

First I edited the UserForm and made the one box "Hidden"
Then created a couple frames and grouped a couple boxes.
Physical Gauges and Side Gauges.

The Physical Gauge selection works good so far.

But I'm not sure how to go about setting up the Side Gauge Selection.

I wanted the user to be able to select at the time of entry what the side gauge relates to innage or ullage, that way the calculation would work. But I couldn't get it to work.

Also, I notice that not all of Section "B" is working the same? When I fill in "B10", it autopopulates "C10" like it should. But the rest of section "B" doesn't do this? Not sure what I'm missing?

I've adjusted it to calculate section D & F at the same time now using:

Please Login or Register  to view this content.

I'm still testing it to see if it's working as needed.

Got dragged away, haven't had a chance to play with it anymore.

Now that I'm thinking about it, I should probably have another ValidateInputForm. Just like the first one, but without the option buttons, so the users can't constantly change INNAGE/ULLAGE options on each cell. Those options only need to be available when the user is entering data into section "B". After that, it's just the straight data input.

How would I go about using multiple intersect options?

Meaning:

If user clicks in range "B10:I10" Is Nothing Then
open userform1

If user clicks in range "B11:I11" Is Nothing Then
open userform2

If user clicks in range "B13:I13" Is Nothing Then

Hmm, I've tried several different ways of making it work the way I'd like it but keep failing miserably. Driving me insane

I blew it all away and came back to the last known working code so I have something to work with again. The problem is, I need to figure out how to make the code realize that:

Please Login or Register  to view this content.

I know what I listed above isn't correct, as I've tried it multiple times, different ways but can't seem to figure it out.

Please Login or Register  to view this content.

OK I finally made something work!!!! I know there's got to be a better way of streamlining the code so its not duplicated isn't there?

Actually thinking of using 3 user forms now, but wanted to figure out how to use 2 first.

Please Login or Register  to view this content.

Ok Heres my last change of the night here. So far it's working almost as I'd like it, some tweaking here and there and it'll get there. There's a couple issues I need to figure out to make so it works correctly. Any feedback on how to clean it up is appreciated.

Please Login or Register  to view this content.

Sorry about that. The last file I uploaded should have had the user-forms I'm using? Let's see what I can come up with today

I can't seem to figure out how this string works? I want to have the Label Read from ROW 2 now, which I figured that part out, but can't seem to be able to figure out how to make it read the correct cell. I want it to read the cell "B6" and then "Open", if I'm working in the "Open" Section of the form, or Read "B6" and then "Close" if I'm in the "Close" section of the form, then read into next section D, F & H when working in those areas.

Please Login or Register  to view this content.

The form is used to track measurements.

So In Cell B6 would go an ASSETS name. Lets say CUP-1.
Then the user goes to CUP-1 and physically measures its liquid contents before something goes into or comes out of it. "OPEN".
The user will take his/her measurements and look up a volume from a chart designed for CUP-1. This volume isn't used on this form. But that physical measurement is added in Section "C"
The user will use the Chart to get a Fixed Gauge Height "The Benchmark, Section B." Hence why B10 and C10 equal the same value, as it will be the same fixed point used for measuring OPEN & CLOSE.
Also on the Asset, CUP-1 there is a computer reading which shows what it's measurement is. This gets entered into Section AA, for comparison purposes.

All sections may need a manual entry if for some reason the scope of the calculations don't fit the criteria.

Also, please don't go out of your way to help. If your busy or enjoying time off, please enjoy the time off. I can wait and play by trial and error in the mean time.

Thanks again,

Originally Posted by Keebellah

Hi, got back yesterday and will take a look at your file this afternoon.
One question, when you select ULLAGE or INNAGE you change the text accordingly but, will you always do the same calculations for column B, D or F?
That means that if you do ULLAGE for B and decide on INNAGE for D the text for that row in Column A will not coincide.

That's correct, the idea is that if a user needs to do a different gauge innage or ullage, they can create a new sheet and use that one for the other measurement.

Thanks Keebellah I'll check this out first thing tomorrow morning. I'm sure I'll a ton of questions as I get into it. Again, appreciate the time you took to help out.

I keep going to play with this but every time I do, I run out of time. Weddings, funeral, work

Anyway, I can't seem to get the grasp of what you did. It doesn't seem to calculate or populate the form anymore? I need to adjust the data validation for the upper, middle and lower text boxes. Those will be for temperatures. I think I figured that out though. You think you can add little explanations to sections of the code, so I know whats doing what?

What are your thoughts on splitting code up and using GoTo if Intersect area is used? I'm thinking that if I use a seperate module for each Asset, CUP1, CUP2 etc. I can better manage the calculations so I know where each sections at, that and also not having to recalculate the entire form each time I adjust 1 measurement for an asset.

Thanks for any feedback and suggestions.