Solved

Excel VBA - form controls to reference programatically

Posted on 2016-11-13
5
47 Views
Last Modified: 2016-11-14
I have a UserForm with several pairs of CheckBoxes and ComboBoxes.

I'd like to write (just) one block of code to make the following happen.

When CheckBox 'chk1' is clicked I want the ComboBox 'cmb1' to become visible.

I would imagine step 1 would be to determine which CheckBox was clicked. I tried Display.Caller but it didn't work (I got a type mismatch error). Step 2 I think would be to reference the control with suffix '1', having picked up the '1' in the control that was clicked. I'm stuck on that.

Thanks for any help.
0
Comment
Question by:hindersaliva
  • 3
  • 2
5 Comments
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 41885520
If the code is in the userform then try Activecontol.Name.
0
 

Author Comment

by:hindersaliva
ID: 41885531
Martin, that works.
So I can find which number it is with Right(ActiveControl.Name, 1) which is '1'.

Now to reference the ComboBox cmb1 ....

"cmb"+ Right(ActiveControl.Name, 1)
How do I put it into Me.??????????.Visible = True
for ???????? to be 'cmb1'?
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41885533
Dim ctl As Control

For Each ctl In Controls
    If Right(ActiveControl.Name, 1)   = "1" Then
        ctl.Visible = True
        Exit For
    End If
Next

Open in new window

0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41885541
I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0
 

Author Comment

by:hindersaliva
ID: 41886344
On testing it I made this small change to make it work.

Dim ctl As Control

For Each ctl In Controls
    If Right(ctl .Name, 1)   = "1" Then       'not ActiveControl.Name
        ctl.Visible = True
        Exit For
    End If
Next

Open in new window

0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

831 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