Linking 2 pieces of vba code

Hi I am hoping someone can help me link the following code please, i am new to VBA and i have managed to get this far by searching various sites but i am struggling with the next bit of code.

What i would like is: if Combobox 1 says "new" then code 1, if not code 2.


Code 1

Please Login or Register  to view this content.

Code 2

Please Login or Register  to view this content.

Am hoping someone can help me.

Hi solus, commandbutton 1 will start the execution.

Michelle.

Sorry you mean execute the form yes it is a button.

Thanks Solus you are a star I have been searching this for ages. I will let you know how I get on, will need to do it in the morning when I'm back in the office.

Hi Solus,

As i said i am a real newbie to this, i thought i had correctly followed your instructions but it doesnt work if, is there something glaringly obvious, i assume its where or how i have labelled the macros Code 1 & 2?? I have placed all the macro in the execution button

Please Login or Register  to view this content.

Thansk for looking at this for me.

Try (note the Ucase functions are to make it non-case sensitive):

Please Login or Register  to view this content.

Hi thanks so much for looking at this. I have loaded the code but its not doing either code 1 or 2? I think i can remove:

Please Login or Register  to view this content.

as i just need it to load when the form opens but either way its not quite right, any ideas???

Please Login or Register  to view this content.

Did this code (that you put in post 1):

Please Login or Register  to view this content.

Work/run?

Hi Yudlugar, yes both codes in post 1 worked independantly (code 1 worked if the checkbox was blank).

I need the users to select "New" from the list in Checkbox , if new, and load code 1 allowing the users to input data which populates the next empty row, or select from one of the options in checkbox 1 to initiate code2 and load the data from the spreadsheet into the userform.

Michelle

You mention in post 3 that the commandbutton should trigger the execution of the code but from what you want I think you need the checkbox change event to edit it? I'd suggest:

Please Login or Register  to view this content.

Hi thats not doing it either, its closing the userform if i chose "NEW" and not loading if i chose an existing. Just put post 1 code back in and both do work but its just this piece to run code dependent upon selection. The userform is initiated on

Please Login or Register  to view this content.

and unloads on

Please Login or Register  to view this content.

Just cant fathom it.

Well what you posted as "code1" ends with

Please Login or Register  to view this content.

which will close the userform. The fact it is getting to that bit means that the statement is working to select which code is required. There isn't anything wrong with the method to run code dependent on selection.

I'd suggest that uploading a workbook would help to find the problem.

VBA Code for Userform.zip

Hi please find attached, it has the original code 1 and 2.

Thanks for looking at this for me.

Michelle

So I think the problem is that your code doesn't do what you want it to, not that you cannot "select" the correct code to run.

-Code1 will load the data from the userform into the first empty row
-Code2 will load the data from the spreadsheet into the userform

You want:
-that when combobox1 is changed, if the value of combobox1 is new it will do nothing, otherwise it will run code2
-When commandbutton1 is pressed, if combobox1 is new it will run code 1, else it will load the userform into the row corresponding to combox1 value, this is the inverse of code 2. Therefore, you need to write a code 3 for that and use:

Please Login or Register  to view this content.

Thanks Yudlugar for your help and patience, I am learning VBA as i go and therefore appreciate all the help and direction. Would you be able to start me off with what code 3 should look like. This piece of work will be vital for the office and i am therefore really keen to get this up and running.

Thanks again.

I think basically instead of:

Please Login or Register  to view this content.

you need

Please Login or Register  to view this content.

Hi Yudlugar, thanks i will give this a go now and see how it goes.

HI Solus, Button2 displays the userform and commandbutton1 loads the form.

Michelle

Hi Solus, yes i have been working on it a while and slowly improving my VBA knowledge at the same time (Emphasis on slowly!). I would like the userform to act as both an input form to capture new data and an update form to add/amend existing information.

So for new data i am looking for the user to select "new" and code 1 would run and this will place all the data entered into the userform into the next empty row.

Purpose 2 is for users to update information by selecting from the dropdown in combobox1(code 2 loads) and they can update - I hadn't actually worked out how to send the data back to the spredsheet yet that was my next step.

Does that make sense?

michelle

As I understood, if the combobox is not new the current record should be overwritten with the new data, rather than a new line.

that was what my last suggestion was for anyway.

Thanks Solus.

My little project is turning into a beast of one!! I have been looking at this constantly. I am going to rename the text and comboxes as you suggested and i will add the second button with the code, be fantastic if you could help me with the code to edit current record. Thankyou so much and thanks Yudlugar as well.

i'll repost the file once i have done all the above.

Michelle

No there will only ever be one line per hospital it will never be duplicated on a different line or added on a different line..

OMG Solus you are an absolute hero and a Genius! I have just opened the file and it and it looks exactly what i was trying to achieve, im just going to transfer it to my workbook and then i will update you. I cant tell you how long i have been spending on this and looking for the answers. How do I reccomend enhance you on this site?

Michelle

Hi Solus, if i enter a new record i am recieving the error message

Variable Not Defined

On thsi piece of Code

Please Login or Register  to view this content.

Hi Solus

I have added the 2 textboxes and cleaned the combobox lists on the 3rd tab and reset the tab orders, thanks for changing the textboxes to the comboboxes for the checkboxes (I originally had these but couldnt work out the code).

I have just updated the code re your last post and i am still receiving an error "

"variable not defined"

and its highlighting

"w.s"

everytime I use a lowercase it converts to uppercase?

Please Login or Register  to view this content.

Hi Solus, definately not on your part, I am in awe of your VBA knowledge.

I have pasted the code but it still says "Variable not defined"

Please Login or Register  to view this content.

Sorry i must be driving you mad.

Thankyou so much for helping.

I tthought so and i did try that but that produced runtime error 9 subscript out of range

Thanks Solus,

Sorry its turned out to be a complex and time consuming one!

VBA Code for Userform.zip

Attatched as requested.

Michelle

Solus, you are amazing and I cant thank you enouh for your patience and time invested in helping me, this was a complex issue for me and i thought I would not be able to resolve it. (Data seems to be pulling from line 3 and skipping Line 2, but after everything i can definately live with that by hiding the row.)

I have added to your reputation, if there is anything else i can do to endorse you please let me know.

Thanks once again.

Michelle

one thing with the checkboxes they could tick more than 1 but only 1 is transferring to the cell. If this is going to be complicated i could revert back to the textboxes i can work the code out for those!

Sorry one more thing..sorry

how do i link these 2

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I really need this locked if data is held as i do not want the inputter to be able to change this.

Michelle

OMG totally daft of me!! Fixed that one which is a relief as its much better with the checkboxes. You'll be pleased to know i resolved my lock cell problem i disabled the box and added the code at the bottom. ( idont want users to be able to change the dates once fixed)

Please Login or Register  to view this content.

Also i have ammended the code slightly so the data if new is inserted into the next empty row, as it was it kept replacing in the same row.

I have learnt so much doing this i surprised myself!! Its absolutely perfect now and will make all our lives so much easier. Thanks again.

back again..! Is there anyway i can list those checkboxes next to each other in the same cell instead of underneath ie. name 1, name 2? Im being picky now as i never thought id get this far, but worth asking to make it uber excellent. Dont worry if not.

Uber Uber!!! Love it, looks amazing and does everything and more than i ever imagined.

Cheeky one now in the % box if the user keys 10 it translates in the worksheet to 1000% if they key it as 10% it shows correctly on the worksheet as 10%. Is there anyway i can force the user to enter the % symbol in the textbox (tbxPercentInstallBase)?