Results 1 to 5 of 5

Thread: UserForm ControlSource Functionality Continues After Closing

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default UserForm ControlSource Functionality Continues After Closing

    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.

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

    Thanks - DC

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,502
    Post Thanks / Like
    Mentioned
    46 Post(s)
    Tagged
    5 Thread(s)

    Default Re: UserForm ControlSource Functionality Continues After Closing

    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.

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm ControlSource Functionality Continues After Closing

    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).

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm ControlSource Functionality Continues After Closing

    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.

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm ControlSource Functionality Continues After Closing

    Oops messed up - above should be -

    Private Sub CloseForm3_Click()
    range("a1") = textbox1.value
    etc etc
    end

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •