Use the Worksheet Change Event
My name is Dasha, I´m living in Germany and I´m new to this forum - chosen because it looked better than the German ones I found so far. So here´s my first question ever - please forgive me if I make mistakes in communicating before I´m more experienced.
I don´t have much knowledge in VBA as yet and mainly want to enhance the user interface to my Excel applications. So I started out with a simple UserForm, but I wanted to have that one appear whenever a certain cell in a certain sheet gets a specific value, and depending on the answer of the user the Form should set the value of some other cell. So maybe there´s a much simpler solution to this task than what I tried - and I´ll be grateful to learn about it.
Anyway I tried the code which follows below - and it works just fine when I call it from the VBE environment - but inside the UserForm it just stops in between (without any error message) when I operate it from the Excel sheet (but still succeeds with the function). I´ll be very happy if someone could solve this "miracle" for me (even IF there´s a better solution altogether).
So I just provided a simple Function which does nothing but call the UserForm and is executed whenever the condition is met (some calls to MessageBoxes in between just have been added to see how far the code gets executed):
Call of the function:Code:Public Function Call_my_form() Call Option_Form.Show MsgBox "Call executed" End Function
The UserForm just uses two OptionButtons with code as follows (two versions to check whether one of these would be the reason - both do behave the same way):Code:=IF(E11="Go";Call_my_form();"Nothing")
So, as said before, when I start the code in VBE, with the cursor before the code of the function, all is fine. If I go to Excel, the function gets called and terminates fine, but nothing happens to cell F13 or acticve cell E11 - I may even stop the code and check the value of "Answer" and the original value of "F13" in VBE and it´s o.k., but then nothing more happens there - though the Box "Call executed" still appears when the Form is closed.Code:Private Sub OptionButton1_Click() Dim Answer As String Answer = "Great work" MsgBox "upper click" ActiveWorkbook.Activate Sheets("Problem").Select Range("F13").Value = Answer MsgBox "value_1 changed" End Sub Private Sub OptionButton2_Click() MsgBox "lower click" Worksheets("Problem").Activate ActiveCell.Value = "allright" MsgBox "value_2 changed" End Sub
So now I wonder what the solution to this riddle may be. Thanks a lot to all who might have a look.
Thanks, Dave, great help with the link and explanations found there - it works fine now. I´ll surely recommend the forum.
P.S.: Still it might help my understanding if someone has a hint where my initial code turned a miracle for me. Thanks again if someone bothers to check.
The problem is that a worksheet function can not alter the contents of any cells.
So yes your code works from VBE but it will fail from the worksheet.
Thanks, Andy, but all the built-in functions do alter cells, and I´m just using a user-defined function. I assume you mean that the only cell which might be changed is the one calling the fuction. Well, this really is something I´d not have guessed as inherent feature/limitation of Excel - so good to know for the future.Originally Posted by Andy Pope
Yes sorry dropped a word there, should read.
can not alter the contents of any OTHER cells
There are currently 1 users browsing this thread. (0 members and 1 guests)