Referring to Controls in Module

Posted on 2014-09-01
Last Modified: 2014-09-01
Hi Experts,

I'm having an issue that I think is pretty simple (and fundamental) but can't find the best way around it...

Basically I have 2 option buttons on a worksheet CyesOPT & CnoOPT

When CyesOPT is true there are a number of controls that I'd like to make visible. Vice versa these controls need to NOT be visible when CnoOPT is true.

The controls are:

voiceOUTopt.Visible = False
voiceDIALopt.Visible = False
recordYESopt.Visible = False
recordNOopt.Visible = False

I've set up a named range and it references CyesOPT so that when it's 'true' it = 1
That range is called 'TELEcontact'

I've then added a module to the Click of CyesOPT that does the following:

Sub CCTyesOPT_Click()
If Range("TELEcontact").Value = 1 Then
MsgBox ("yes, the value is true!") ' this works fine!

'When I get to the following:
voiceOUTopt.Visible = False
voiceDIALopt.Visible = False
recordYESopt.Visible = False
recordNOopt.Visible = False
'I get the error 424 Object Required

Do I need to declare the objects?
THe worksheet is called Technology and I've tried to indicate what sheet the controls are on but the error persists.

I think this is probably something very simple and will be fundamental to my model. Any help appreciated.


Question by:martywal
    LVL 33

    Expert Comment

    You need a worksheet reference.

    How did you create the controls?

    Was it through the Forms or ActiveX control toolbar?

    Also, what's the codename of the worksheet(s) they are on?

    Author Comment

    Thanks imnorie!


    I used the insert FormControls in Excel 2010
    LVL 33

    Expert Comment

    Can you attach a sample workbook?

    It doesn't really need to have any data, just the checkboxes.

    Author Comment

    Hope this helps...
    Basically when the 'Is contact centre telephony required = YES
    The controls in the grey (and the rows) need to be visible/unhidden
    Thanks again for your help
    LVL 29

    Accepted Solution

    Try this:
    With Worksheets("ORF - Technology").Shapes
        !TECHvoiceDIALopt.Visible = False
        !TELErecordYESopt.Visible = False
        !TELErecordNOopt.Visible = False
        !TelePurpCOMPopt.Visible = False
        !TelePurpQAopt.Visible = False
    End With

    Open in new window


    Author Closing Comment

    That worked a treat!
    Thanks really appreciate your help on this!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now