Correct Sheet Reference & Code not working?

[liquidmettle]

Hello Everyone!

I have developed a macro for a userform that I feel is close to what I want to achieve but I haven't figured out a few issues just yet:

I will have 30 sheets this code needs to run on (named imaginatively 1-30), and I'd prefer the macro to grab the current sheet's reference (the sheet the button was pressed on), and use that to execute the rest of the code.

Where I'm having problems:

-Macro determining name of sheet and using that as the sheet the referred to range is on
-My "estimate" value keeps telling me "Type Mistmatch"
-Getting the data to paste in the sheet

As it stands I know the if/then statements telling users they must enter the required fields works.

Its when I added the code to try to make each new entry paste to the next available line that the macro didn't work (I didn't get an error on this, it just didn't paste the data entered).

Please Login or Register  to view this content.

Thank you all in advance!!

[MarvinP]

Hi,

Are you simply looking for the Activesheet that the button was pressed on? See the attached that does that.

[liquidmettle]

Hi Marvin,

That does indeed look like it will solve the reference issue (wasn't sure how to refer to it and the Dim as String just slipped my mind).

Here is the updated code:

Please Login or Register  to view this content.

I also fixed the Type Mismatch error I was having by just changing the order the process occurred and getting rid of the special variable.

Now for the remaining problem: Entries are still not posting to the sheet. I ran the code above with Marvin's addition. No errors but it also did not paste the data entered into the userform onto the spreadsheet.

I realized since I have merged cells my offset ranges might not be correct. So I tried changing my offset ranges to:

Please Login or Register  to view this content.

Still no dice.

Is there something I'm missing in the following code to paste the user entered values into the sheet?

Please Login or Register  to view this content.

This is the relevant pieces of code pulled from the larger code. What may I be missing?

Thanks!

[MarvinP]

Hi,

Set a breakpoint in your code and step through it. Make sure "rowcount" is the value you think it should be. Also try to use "Me.ComboBox1.Value" to show the values are coming from the UserForm.

[liquidmettle]

Hi Marvin,

You're spot on with these fast replies : ).

How might I go about setting a breakpoint? Or do you mean set up code I know will fail?

I've seen the "Me.Object.Value" code before but I'm not sure exactly how it differs. Could you give me a quick explanation?

[MarvinP]

You set a breakpoint by clicking in the left margin of the code. A big red dot will appear and your code will stop on that line. You can then step through the code or hover over variables and see what values they are.
Look at http://www.wiseowl.co.uk/blog/s196/breakpoints.htm for setting a breakpoint.

Me. refers to the current UserForm.
http://www.fontstuff.com/ebooks/free/fsUserForms.pdf shows a good Userform tutorial and uses the Me construct.

[liquidmettle]

Hi Marvin,

Firstly, your second article helped me figure out part of my problem (I was missing a .Value on all of my offsets!).

Secondly, I've met with about 15% success : p

When I run the code now, my first offset (putting combobox1's value into the correct cell) works like a dream. However, none of the other information is captured on the sheet.

What might I be doing wrong in this section of the code? I ran a breakpoint as you suggested and all the values were what I manually entered into the respective boxes.

The "rowcount" variable was equal to 0, but I'm not sure if that's a problem or not. Every time I run the userform from the initialize event, only Combobox1's value gets entered onto the sheet.

However one of my problems might lie in the data being pasted into merged cells. I've tried the offset values as 0-7 and zero through the first cell in each merged range. Neither options have worked.

Your last post got me closer, just need to figure out what human error I'm introducing into this code.

Please Login or Register  to view this content.

One additional question: I figured out through trial and error that my starting range was J9. But the first cell I want data entered into is J12. When J12 was set as the range, data was put into cell j15. Basically I don't understand why there is a three row gap created by the code.

[MarvinP]

OK - It looks like you are getting closer.
Instead of using offsets from J9, why not plug the userform values into normal range or cell notation in your code.

Range("J9").Offset(0,1) is really Range("K9")
I've standardized my code to use Cells notation on this and like Cells(9,"K") better.

So in your above

Please Login or Register  to view this content.

I'd write this as:

Please Login or Register  to view this content.

I also wonder if you might need .Text instead of .Value for textboxes??

[liquidmettle]

Hi Marvin,

So as I mentioned earlier, user-error is just how I roll : p. When I was counting the offset ranges, I didn't factor in J-column being zero, so all my ranges were 1 number off. I've corrected the offset ranges and Huzzah! all data pastes exactly where it should (p.s. I did not need to put .text, .value did end up working).

*Edit*

This post originally had different text here, stating another problem. I have since solved the problem! (Woo Hoo!). Everyone appears to be working 100% as it should.

Thank you Marvin for the advice and sources that guided me to work out the bugs. It is truly appreciated!!!