UserForm Textbox Contents / ActiveCell / ClearContents

Hi all,

I have a textbox in a userform which when the userform is opened should always equal the contents of the active cell. I am having a nightmare getting this to work though.

Please Login or Register  to view this content.

this is my code however it only works the first time you open the userform, when opening again the textbox contains the information from the first activecell, no matter which cell you select. I tried using code to clear the contents of the userform when it's closed to see if it would clear the contents and trick the userform into getting a new activecell however if i put that in the code then when it opens it is always blank!

Thanks in advance for any help, i thought this would be simple but it's driving me mad!

Hi, will try and upload a sample workbook when I get home. Is there a conflicting code that could affect it? Application screen updating etc.

Had problems before when people have posted solutions which work on sample workbooks but not my original so just wondering if somewhere there is a conflicting code.

Originally Posted by Fluff13

There could be, but if that's the only code in the initialize event, I wouldn't have thought so.
Unless you also have code in the Activate event

Hi, no other code in the activate and the only other code in the initialise event is to position the userform.

I’ve noticed however since that the text box is updating with the active cell but only on the second attempt. For example if I select C1 then C1’s contents are populated in the text box, if I then select F1, it is still C1, but if I select F1 again, the text box updates to F1. Its like there is a delay in excel understanding the active cell has now changed. Any ideas? Is there a way I can refresh the userform or point to something more specific than active cell?

I think we need an example file here to test.

On opening in which Mode is that done ? (can you select the worksheet when userform is opened)
When closing the userform do you Hide it or Unload it ?

I managed to get it working with the code below added. I think the issue was trying to start it on Userform initialize? It worked the first time when I opened the sheet but i think initialize only seems to work the first time you open the Userform and does not work on any other times you try and open it.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

So this has worked however I have another issue in the same Userform which I believe is from the same issue when trying to update something on Userform Initialize, not sure if this should be in a different thread or not but within the same Userform I have a ComboBox named "DropBox" linked to a dynamic named range in the initialize event.

Please Login or Register  to view this content.

Similar to the first problem, when i change the dynamic range the combobox does not update with the new dynamic range if the userform has already been opened. If i close the workbook and reopen it then the dynamic range in the combobox has been updated as it is the first time the userform has been opened. Is there a way i can refresh the userform so that every time it is opened it will get the new dynamic range?

Initialize only kicks in when the userform is closed and the opened, not when it's only hidden and reactivated.

Also when the userform was opened modeless the Initialize event only works on opening.

Hi Bakerman, Thankyou! this has been driving me mad, i knew it would have been something simple to fix, all the issues I had were due to my background code hiding the userform, not closing it with the Unload function, i have updated all the code to Unload and now it all works fine. I didn't realize the different between the Hide and Unload function.

Marked as solved and rep added.

Good you sorted it out. (just needed a little nudge in the right direction )

Thanks for rep+.