Compile Error Variable Not Defined when trying to submit a VBA Userform

[dharvey1978]

Hi Excel Forum,

I have written some VBA Code and have worked out most of the issues i was having but i now have a Compile error Variable not defined coming up.

I have a userform and when i hit submit it is coming up with the error. I have a combobox1 dropdown which has locations in it and i want it to go onto that sheet along with other textboxes and comboboxes i want to go into certain cells on the sheet as well that i'm not sure if that is causing the issue or it something else.

Sorry no sample file available hopefully you are able to help from the code below

Please Login or Register  to view this content.

TIA
Donald Harvey

[6StringJazzer]

When asking help with an error, always indicate the line of the code that is highlighted when the error occurs. Otherwise we are looking for a needle in a haystack.

[torachan]

Try altering one or both lines to below.
Failing that see big yellow banner - upload your workbook - remove sensitive data.

Please Login or Register  to view this content.

[6StringJazzer]

Please Login or Register  to view this content.

I think you need to omit the quotes, that's a variable name

Please Login or Register  to view this content.

[rorya]

Shouldn't you be using shSheet, not sh? Also, there's no point to something like "B" + 2 when you can just use "B2"

[TMS]

Suspect this

Please Login or Register  to view this content.

should be this

Please Login or Register  to view this content.

[Andy Pope]

in addition you probably want to replace the fixed reference to row 2 with the last row number you load in to the variable iCurrentRow.
As well as removing the sh reference within the WITH block. and finishing that block using END WITH instead of END IF

Please Login or Register  to view this content.

[dharvey1978]

Hi,

I have made the changes i think mentioned above now when i submit it comes up with a Run-Time error 13: Type Mismatch when i cllick debug it highlight this section

Please Login or Register  to view this content.

I have also attached a sample workbook as well

the full code is now

Please Login or Register  to view this content.

[rorya]

You need:

Please Login or Register  to view this content.

[dharvey1978]

Hi,

I can see its now adding it into the spreadsheet, but when you go to add another entry it is overriding the previous entry, I want it to be on the next line down and so on for each entry after.

Thanks

[rorya]

It appears you aren't populating column A so you should use B to calculate the next row:

Please Login or Register  to view this content.

[dharvey1978]

Thanks Rorya, that has worked for me thanks again.