Announcement

Collapse
No announcement yet.

Function Call to UserForm (VBA)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Function Call to UserForm (VBA)

    Hi folks.

    My name is Dasha, Im living in Germany and Im new to this forum - chosen because it looked better than the German ones I found so far. So heres my first question ever - please forgive me if I make mistakes in communicating before Im more experienced.

    I dont 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 theres a much simpler solution to this task than what I tried - and Ill 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). Ill be very happy if someone could solve this "miracle" for me (even IF theres 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):

    Function:

    Code:
    Public Function Call_my_form()
    Call Option_Form.Show
    MsgBox "Call executed"
    End Function
    Call of the function:

    Code:
    =IF(E11="Go";Call_my_form();"Nothing")
    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:
    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, 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 its o.k., but then nothing more happens there - though the Box "Call executed" still appears when the Form is closed.

    So now I wonder what the solution to this riddle may be. Thanks a lot to all who might have a look.
    Best regards.
    Dasha.

  • #2
    Re: Function Call to UserForm (VBA)

    Use the Worksheet Change Event

    Comment


    • #3
      Re: Function Call to UserForm (VBA)

      Thanks, Dave, great help with the link and explanations found there - it works fine now. Ill surely recommend the forum.
      Best regards.
      Dasha.

      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.

      Comment


      • #4
        Re: Function Call to UserForm (VBA)

        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.

        Cheers
        Andy

        Comment


        • #5
          Re: Function Call to UserForm (VBA)

          Originally posted by Andy Pope
          The problem is that a worksheet function can not alter the contents of any cells.
          Thanks, Andy, but all the built-in functions do alter cells, and Im 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 Id not have guessed as inherent feature/limitation of Excel - so good to know for the future.

          Bye.
          Dasha.

          Comment


          • #6
            Re: Function Call to UserForm (VBA)

            Yes sorry dropped a word there, should read.

            can not alter the contents of any OTHER cells

            Cheers
            Andy

            Comment

            Working...
            X