Forum Discussion

Riley_Johnson's avatar
Riley_Johnson
Copper Contributor
Apr 25, 2024

Can you manipulate the contents of a sheet while a Userform is loaded and shown?

This might be a dumb question, but it is the only solution I can think of to my problem.

 

I have a Userform that takes in a series of range addresses via the refEdit control. One of the tests I run on these addresses is whether values input into a cell throw an error in another cell. If I call this function while my user form is loaded and shown, the user form disappears. However, I don't think it fully unloads. I usually have to hit the reset key before I re-run the Userform if I want it to work properly.

 

To see if this was a refEdit control bug, I ran my function with the range addresses as string literals, and it still causes the same issues. So, I don't think this is from a refEdit control bug. Moreover, when I comment out the lines in the function that write contents to the cells, the function does not crash the Userform. (Neither does the function serve it's purpose.)

 

I'm writing contents to cells using the following syntax:

 

 

Range(myAddressAsString).Value = MyValue

 

 

The only conclusion I can think of to explain the behavior is that you cannot edit cell values while a Userform is running. You can read from them, and I've made a few functions that retrieve the contents of cells that do not crash the Userform. But every time I try to write to cells, my Userform collapses.

 

More details on the actual function are at this discussion. I've also included the file with all the Macros there if curious.

  • You can manipulate cell values while a UserForm is open, but issues can arise from recalculations, event conflicts, or form visibility changes. Use error handling, manage screen updates, and debug your code to identify and fix the issue. Avoiding complex controls like RefEdit and ensuring proper handling of the UserForm's visibility should help stabilize your application.

Resources