UserForm ControlSource Functionality Continues After Closing

DomesticedCrab

New Member
Joined
May 16, 2019
Messages
7
Hi -

I have created a UserForm that features roughly 50 inputs. The same UserForm is utilized on 10 sheets, each the exact same layout but the User may input different values on the UserForm dependent on the sheet they are on.

I created one UserForm, whose ControlSource does not reference a specific sheet, but instead references a Cell, such as B119, etc.

This way, when the UserForm is called on the different sheets, it'll function for each.

My issue is, after closing the UserForm, and working on other sheets all contained within the same file, formulas (such as =B1+B2) are replaced by their values (i.e. =B1+B2 disappears and the cell is the sum of those cells) thus the cell is no longer dynamic with the formula and is static with a single value.

From looking into it, it appears the area on my sheet where this error occurs is the same location where my UserForm would be replacing values when called. It seems to be some sort of Paste Special occurring.

I utilize a control button for showing the userform (Userform.show), and a close button to exit out of the Userform, "Unload Me".

I can not get this unique error to replicate on any consistent basis. :confused:

Is the "Unload Me" function not the correct way to "close" a UserForm? Any thoughts?

Thanks - DC
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
That is one reason that I never use the ControlSource property. You will find that this formula replacement can happen even in different workbooks to the one that has the form in it.
 
Upvote 0
Thanks for the reply - sounds like this isn't readily resolvable.

In lieu of the ControlSource, I'll work to implement a macro that grabs the Textbox values, and places them directly into the desired cell locations when the UserForm closes, and vice versa when the Form opens (in the event a user overrode the cell directly).
 
Upvote 0
Just to follow-up if anyone finds this thread and has the same question -

I implemented logic similar to the below in my Userform

Private Sub Userform_initialize()
TextBox1.value = range("a1")
etc, etc
end

I also added a "close button" that would complete the following...

Private Sub CloseForm3_Click()
range("a1") = range("a1")
etc etc
end

This way if a User did not use the form their changes on the sheet cell will impact the Form, and vice versa.

I removed all the ControlSource cells and my errors have disappeared.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top