+ Reply to Thread
Results 1 to 5 of 5

TextBox/ComboBox comparison variables not working as expected.

  1. #1
    Registered User
    Join Date
    03-14-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    4

    TextBox/ComboBox comparison variables not working as expected.

    Hi everyone,

    This is a question I am even unsure to ask as I may ask it wrong.
    I am currently working on an excel workbook that contains 2 tabs of data, there are 2 more tabs but are irrelevant to this problem.
    The first tab has "last Years" data in it, the 2nd tab contains new data written to it by UserForm1.
    Userform1 is made up of textboxes and comboboxes(When adding new data)
    I have used: With rngTarget.Cells(1, 1).EntireRow to assign various values from within the tab to public variables.
    for example: varSurfaceLocation = CStr(.Cells(1, 17)) - Column 17 being the one with the data in it for this particular variable.

    When referencing the sub I used ActiveCell to select the row.

    The Userform textboxes are then populated with the values from the variables.
    UserForm1.TextBox1.Value = varSurfaceLocation

    This is all done within the module.

    Then I have a private sub named UserForm_Initialize() which assigns the value from the above variable to another private variable
    Location = varSurfaceLocation

    Once a checkbox has be checked the user is able to edit the textbox and change the value.
    In another private sub named TextBox1_AfterUpdate() the TextBox1.Value (User just adjusted) is written into the public variable
    varSurfaceLocation = Me.TextBox1.Value

    The public variable and the new value are compared and the backcolor of the textbox changes to yellow if they are different (Which they usually are as the new one is an adjustment), if the user was to change the value back to be the same as the original entry then the backcolor of the textbox will go back to being white.

    This all works perfectly the first time it is run. However, when the user clicks another cell in the spreadsheet the textboxes are all re-populated with the new data which works fine. The problem is when the user again changes a value in the textbox as it seems that the comparison variable still has the old data from the previous selection in it.

    Please help me.
    I hope it's not too confusing to read but I will answer any follow up questions.
    Or I could upload my workbook, it is 755kb in size and obviously has macro's attched to it.

    Andy
    Last edited by Bolton9190; 03-18-2013 at 06:02 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TextBox/ComboBox comparison variables not working as expected.

    Andy

    I think it would be a good idea to attach your workbook.

    Then we can see what's happening, and in particular what you are actually comparing.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    03-14-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: TextBox/ComboBox comparison variables not working as expected.

    Hi Norie,

    Thanks for your responce, I have however fixed the problem. The comparison variables were only being loaded with data when the userform initialized so when selecting new cells in the background they were'nt being re-populated.
    I have made them into public variables and added the value population to the initial load sequence and all is good again.

    Thanks
    Andy

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: TextBox/ComboBox comparison variables not working as expected.

    Andy

    Glad you got it sorted.

    Not sure you need public variables though - can't you just compare the textbox/combobox values directly.
    Last edited by Norie; 03-15-2013 at 05:43 PM.

  5. #5
    Registered User
    Join Date
    03-14-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: TextBox/ComboBox comparison variables not working as expected.

    Norie,

    You are right, luckily I had gone on dinner so I hadn't changed all my code. I have tried your suggestion and I am now comparing the .value of the textbox/combobox with the original variable and it works. No need for extra variables now, either public or private.

    Thank you ever so much.
    Andy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1