+ Reply to Thread
Results 1 to 13 of 13

Initiating Subrotines in VBA

  1. #1
    Registered User
    Join Date
    10-05-2015
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    11

    Initiating Subrotines in VBA



    Hello Everyone,

    I would greatly appreciate if someone would check my code and tell me why the subroutine "QTRRMS" works bu the code that calls the subroutine does not.

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("QTRRevModSel")) Is Nothing Then

    Call QTRRMS

    End If

    End Sub

    Sub QTRRMS()

    If Range("QTRRevModSel") = "Aggressive Model" Then
    Range("Selection") = 1
    ElseIf Range("QTRRevModSel") = "Standard Model" Then
    Range("Selection") = 2
    ElseIf Range("QTRRevModSel") = "Conservative Model" Then
    Range("Selection") = 3
    ElseIf Range("QTRRevModSel") = "Enter a Roll Your Own Number" Then
    Range("Selection") = 4
    ElseIf Range("QTRRevModSel") = "Splash a Number" Then
    Range("Selection") = 5
    Else
    Range("Selection") = 6

    End If

    End Sub

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Initiating Subrotines in VBA

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    10-05-2015
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    11

    Re: Initiating Subrotines in VBA



    Hello Everyone and especially Norie,

    I was unaware of rule 3 as I never looked at the rules to post code. Sorry about that.

    However, I would greatly appreciate if someone would check my code and tell me why the subroutine "QTRRMS" works (using an "F5" to check the code) but the code that calls the subroutine does not. The same thing happens when I close the VBA editor. I have to go back into the editor and run the sub QTRRMS manually.

    Thanks for the help new info: BTW "QTRRevModSel" is located at cell "A5". It is a data validation cell.

    Please Login or Register  to view this content.
    Last edited by rjgoldstein; 10-14-2015 at 10:32 AM. Reason: additional information

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Initiating Subrotines in VBA

    Do you mean that the worksheet change event isn't calling the sub QTRRMS?

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Initiating Subrotines in VBA

    Is that code in the correct worksheet module?
    Remember what the dormouse said
    Feed your head

  6. #6
    Registered User
    Join Date
    10-05-2015
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    11

    Re: Initiating Subrotines in VBA

    Noire, Yes that is exactly correct

  7. #7
    Registered User
    Join Date
    10-05-2015
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    11

    Re: Initiating Subrotines in VBA

    Romperstomper,

    Please see the screen shot I have uploaded
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-12-2015
    Location
    Bristol, UK
    MS-Off Ver
    Office 365
    Posts
    39

    Re: Initiating Subrotines in VBA

    BTW "QTRRevModSel" is located at cell "A5". It is a data validation cell.
    Could you please explain what you mean by data validation cell?

    To note, the Event Worksheet_Change will not fire if this cell contains a formula and the value of the formula changes only because the values of the cells it is linked to change. The event should normally fire if the cell value is manually changed in the worksheet, or changed through VBA.

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Initiating Subrotines in VBA

    Have you put a breakpoint in to see if the event is being triggered at all?

  10. #10
    Registered User
    Join Date
    10-05-2015
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    11

    Re: Initiating Subrotines in VBA

    UnderscoreBadger,

    I am not sure that I understand your comment above. I have other subs that are fired off by a Data Validation cell. In fact there are other examples on the web such as
    http://stackoverflow.com/questions/1...tion-drop-down

    My drop down menu is located in "A5" and it consists of 6 different text selections. I have done this before and it works. In this case I have not found what I did wrong. the subroutine works fine. It is just the subroutine call that is not working.
    I tried setting up from a text entry in a cell and that did not work either.

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Initiating Subrotines in VBA

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  12. #12
    Registered User
    Join Date
    10-12-2015
    Location
    Bristol, UK
    MS-Off Ver
    Office 365
    Posts
    39

    Re: Initiating Subrotines in VBA

    Ah understood. Thanks rjgoldstein. The event should indeed trigger from a drop-down.

    As romperstomper has asked, does the event fire at all? If not, then perhaps check if Application.EnableEvents is set to True. I have in the past left it switched off and scratched my head wondering why my events weren't triggering.

  13. #13
    Registered User
    Join Date
    10-05-2015
    Location
    Michigan
    MS-Off Ver
    2013
    Posts
    11

    Re: Initiating Subrotines in VBA

    Thank you everyone I found the issue. It was an excel addon for my BI tool that is not functioning as expected. The data validation tool with the BI addon does not operate the same way the excel data validation does. Therefore the sub being called by the line

    Private Sub Worksheet_Change(ByVal Target As Range)

    was never really called. I have to develop a workaround for that issue.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find Object Initiating Sub
    By domino111 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2014, 03:53 PM
  2. Excel initiating email to Outlook automatically
    By dbement in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2012, 12:25 PM
  3. Initiating an AddIn through VBA
    By Kaziglu Bey in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-26-2010, 01:21 PM
  4. Initiating a macro in a worksheet without a command button
    By Macster in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-22-2009, 05:22 AM
  5. [SOLVED] initiating a macro
    By johnT in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-02-2005, 11:06 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1