Solved

Excel Active X controls acting up and not letting me check the checkbox

Posted on 2014-03-08
5
3,892 Views
Last Modified: 2014-03-16
I am creating a form in Excel 2013 using activeX controls.  I use VBA code to resize the rowheight to shrink and grow the areas that have more detail.  So if you check the checkbox beside a category, it opens up and unhides the controls associated with it.  Basically more check boxes and labels, as well as a text box for the "other" field.

The checkboxes have unique names and captions.  When I check the checkbox it opens up the row to show the checkboxes, but only about half of them can be checked.  The ones that can't flash briefly further down the page, but won't check.  The text boxes won't take data either.

If I go into design mode and move the check box slightly, then get out of design mode they will work.  Until I shrink the row with the associated checkbox, then when I expand it, they stop working again.

The only thing I noticed is that the "top" property for the checkbox has a value that is duplicated on other checkboxes.  When I move it around in design mode, it changes, but after the problem recurs, it is back to it's original duplicate value.

Here is a sample of the vba code I use to change the row height:


Private Sub cbReports_Click()
 If cbReports = True Then
Rows("24:24").Select
    Selection.rowheight = 100
    ActiveSheet.cbReportsphoto.Visible = True
    ActiveSheet.lblreportsphoto.Visible = True
    ActiveSheet.cbreportsfull.Visible = True
    ActiveSheet.lblReportsFull.Visible = True
    ActiveSheet.lblreportcopies.Visible = True
    ActiveSheet.lblReportscopyReq.Visible = True
    ActiveSheet.cbReportsCopyReq.Visible = True
    ActiveSheet.lblReportsCopyCust.Visible = True
    ActiveSheet.cbReportsCopyCust.Visible = True
    ActiveSheet.lblReportsCopyOther.Visible = True
    ActiveSheet.cbReportsCopyOther.Visible = True
    ActiveSheet.tbReportsCopyOther.Visible = True
   Else
    Rows("24:24").Select
    Selection.rowheight = 25
    ActiveSheet.cbReportsphoto.Visible = False
    ActiveSheet.lblreportsphoto.Visible = False
    ActiveSheet.cbreportsfull.Visible = False
    ActiveSheet.lblReportsFull.Visible = False
    ActiveSheet.lblreportcopies.Visible = False
    ActiveSheet.lblReportscopyReq.Visible = False
    ActiveSheet.cbReportsCopyReq.Visible = False
    ActiveSheet.lblReportsCopyCust.Visible = False
    ActiveSheet.cbReportsCopyCust.Visible = False
    ActiveSheet.lblReportsCopyOther.Visible = False
    ActiveSheet.cbReportsCopyOther.Visible = False
    ActiveSheet.tbReportsCopyOther.Visible = False
    End If
End Sub

You can see in the attached file screenshot, where the outside lab services checkbox has an errant check box below it. That ONLY appears when the mouse button is held down clicking on the Other checkbox above it that is clicked and it never unchecks.

Ideas?
screenshot.jpg
0
Comment
Question by:oncall4you
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39915276
Are your controls set to "Move but don't size with cells?" If not, try that setting.

You should be aware that the expert community regards ActiveX controls as buggier than Forms controls, and unexpected movement and size changes are frequently occurring issues. I use Forms controls whenever I have a choice.

When you do want help with an issue like this, please post a sample workbook that reproduces the problem. You'll get much better advice if we can reproduce the problem and develop workarounds for it at our end.

If you stick with ActiveX controls, you can simplify your code to the following. Note that the revised macro does not speak to your problem, however.
Private Sub cbReports_Click()
Dim b As Boolean
With ActiveSheet
    b = .cbReports = True
    .Rows("24:24").RowHeight = IIf(b, 100, 25)
    .cbReportsphoto.Visible = b
    .lblreportsphoto.Visible = b
    .cbreportsfull.Visible = b
    .lblReportsFull.Visible = b
    .lblreportcopies.Visible = b
    .lblReportscopyReq.Visible = b
    .cbReportsCopyReq.Visible = b
    .lblReportsCopyCust.Visible = b
    .cbReportsCopyCust.Visible = b
    .lblReportsCopyOther.Visible = b
    .cbReportsCopyOther.Visible = b
    .tbReportsCopyOther.Visible = b
End With
End Sub

Open in new window

0
 

Author Comment

by:oncall4you
ID: 39920174
As requested here is a generic version of the code.  I zipped it because it is in xlsm format, and it might have blocked it.
Generic-prototyping-form.zip
0
 

Accepted Solution

by:
oncall4you earned 0 total points
ID: 39920222
I FOUND IT!!  Eureka.  The solution was to change the horizontal and vertical size on the checkboxes that weren't working.  By making them just a little bit larger, they now all work.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39920227
For your workbook, you can achieve better results by doing the following to each checkbox, label and textbox control:
1.  Enter Design mode
2.  Rightclick the control and choose Format Control... from the resulting dialog
3.  On the Properties tab, choose the option to "Move and size with cells"
4.  For the controls that are unhidden when you check the boxes, you will need to reposition them when they are visible.
0
 

Author Closing Comment

by:oncall4you
ID: 39932360
Changing the size of the checkbox allowed it to be checked.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 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