Writing values from forms to sheet

**Mike_e** · 10-12-2011, 05:23 AM

Hi,

I'm fairly new to using VBA and have a problem writing back values from a form to a worksheet. Here's what I've done

When the form initializes this occurs

Dim MyRange as range
Set MyRange = Sheets ("Sheet1").Range ("A1")

I have a control on the form which, when updated, I'd like to store the value in the above range therefore within the controls subprocedure I have

Private MyControl_Afterupdate()

MyRange = MyControl.Value

End Sub

When I enter a value in the form I don't get any error messages but I'm also not getting the value stored in the worksheet. Any help would be appreciated

Many thanks

Mike

**royUK** · 10-12-2011, 05:39 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**watersev** · 10-12-2011, 05:50 AM

hi, Mike, try to put it this way:

Please Login or Register  to view this content.

**Mike_e** · 10-12-2011, 06:01 AM

Many thanks for your speedy response Watersev. I've amended the code as suggested but this doesn't work. I get the "runtime error" 424 Object Required message when I change the contents of the control.

the only way I 've been able to get this working so far is to put the following into the controls procedure

Please Login or Register  to view this content.

I would have thought that once I'd declared the range when the form initializes, I shouldn't need to do it again.

Many thanks again

**Kyle123** · 10-12-2011, 06:20 AM

Note the .Value

Please Login or Register  to view this content.

Although this could be shortened to:

Please Login or Register  to view this content.

**Kyle123** · 10-12-2011, 06:23 AM

Just re-read your code, your range declaration

Please Login or Register  to view this content.

Should be above all your subs/functions, right at the top of the page.

Also use option Explicit at the top of the page, this will show up any of these errors