Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Function Call to UserForm (VBA)

  1. #1
    Dasha Guest

    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:

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

    VB:
    =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):

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

  3. #3
    Dasha Guest

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    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


  5. #5
    Dasha Guest

    Re: Function Call to UserForm (VBA)

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Userform To Call Up Data From Workbook
    By Ogilvy in forum EXCEL HELP
    Replies: 1
    Last Post: April 11th, 2008, 10:23
  2. Replies: 2
    Last Post: March 9th, 2006, 08:37
  3. VBA Call function
    By brenny in forum EXCEL HELP
    Replies: 7
    Last Post: July 29th, 2005, 07:44
  4. Replies: 8
    Last Post: October 13th, 2003, 08:00
  5. Call UserForm From Another Workbook
    By Phil in forum EXCEL HELP
    Replies: 4
    Last Post: August 8th, 2003, 07:22

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
  •  
porno