Double click row to return data to userform for edit then return back to sheet

Hi all!

I thought I almost had this sorted...

I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.

I also need to be able to:

• Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).

• Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)

I have attached my sheet - the code is very messy (basic knowledge of VBA). Can someone tell me where I am going wrong?

Thanks in advance!

Lots of errors (Sorry)

Try changing these first:

From

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

There are other issues...

Please Login or Register  to view this content.

No idea what this is supposed to represent. Perhaps

Please Login or Register  to view this content.

...?

And

Please Login or Register  to view this content.

As mentioned, Datarange is ambiguous so can't figure out what this should be...

Probably more, but there's enough there to be going on with.

Thanks for the reply Cytop.

In reference to your points:

It was supposed to be a range reference so basically if I double-click on a range anywhere between $A$4 and xlDown and xlRight (or K6 as it is on my attachment) then it brings up the userform containing data from that row. If the work has not been completed OR it's in progress, we shouldn't be invoiced for it therefore those fields are dependent on the value in cells under column G. This value will need to be changed to "Yes" once the work is complete and will be done from the userform.

Originally Posted by cytop

To

Please Login or Register  to view this content.

Changed. This is supposed to be a range reference - so if I click in A4 then the results of A4 appear in the userform and I can edit them. If work is complete then I click on the relevant radio button, it changes the value to "Yes", it enables the Invoice Number and Actual Cost fields and dumps the values for those into the sheet. I also want a calculation field to be entered but I can do that.

Originally Posted by cytop

There are other issues...

Please Login or Register  to view this content.

No idea what this is supposed to represent. Perhaps

Please Login or Register  to view this content.

...?

This was me trying to figure out how the code behaved. If I double-clicked in column G, it brought back everything in that row to the userform (which is great as that's what I want) however because I clicked in G, the "Work Complete" fields were available. This only applied to column G whereas I want it from A to the last cell.

Originally Posted by cytop

And

Please Login or Register  to view this content.

As mentioned, Datarange is ambiguous so can't figure out what this should be...

Probably more, but there's enough there to be going on with.

Again me trying to work out how the code behaves.

Appreciate everyones help on this...

Originally Posted by Cytop

Please Login or Register  to view this content.

I added this.

Originally Posted by Cytop

A new procedure in the Quotes sheet class module

Please Login or Register  to view this content.

I added this - Right-click on Quotes Sheet->Insert->Class Module

Originally Posted by Cytop

A new variable and Public Property in the userform

Please Login or Register  to view this content.

Where do I add this? Is this a new sub? A new class module?

Originally Posted by Cytop

And, finally, the 'Submit' button Click event

Please Login or Register  to view this content.

This bit has been added to my "Submit" button...

It's just the public property bit I'm confused about...thanks again for your help!!

Here is my sheet with what I could grasp from the above...

So far so good. Put all the bits of code you provided in the right areas but now I'm gettin a very bizarre error message when I click "Cancel":

Run-time error '-2147418105 (80010007)'
Automation error
The callee (server [not server application]) is not available and disappeared; all connections are invalid. The call may have executed.

When I debug, it goes to:

Please Login or Register  to view this content.

About to see what this error means but do you have any idea?

It refers to something about "me.XXXX". Totally confused...

Will get back to this a little later - on a phone at the moment but you probably unloaded the userform rather than my code doing it...

OK, thanks a lot.

OK, really confused now.

I kept the ValidateInput code the same as you suggested however when I run this:

Please Login or Register  to view this content.

...the error message pops up regardless of if txtCofelyReference.Value is blank or not.

Try the attached.

The original code I posted was written freehand and was untested - obviously there were 1 or 2 issues. Also, some of the mods you made had errors as well so I passed a train journey going through it...

Tested it at home and worked perfectly. There were a few type mismatch errors but I gave it a dry run and seemed OK after a while. I'll check it at work tomorrow but if it works there I owe you a cyber beer. Thank you so much for your help!

Please Login or Register  to view this content.

Is there a problem with that line...?

Just got to work now and about to test it...

Yeah, tried it and still getting a type mismatch but at work it's not letting me debug - at home last night it did and was giving the bit of code i posted above as the culprit!

It's still being submitted by the way. It works within reason, just the type mismatch error...

Remove that line - left in by mistake. The field is updated in the IF block above it.

You should note this needs error handling added. The function CCUR() was used in case the users enter a £ sign in front of the numbers. CCUR will evaluate that correctly as a number but falls over if any other non-digit text is entered.