?
Solved

Using VBA for Excel 1) Data Validation code not working reliably and 2) need help with checkboxes

Posted on 2010-01-09
8
Medium Priority
?
743 Views
Last Modified: 2012-05-08
My son has asked me to help him with coding two problems on an Excel spreadsheet that is used by his department for accident investigations.

The first problem is to supply data validation for a combo box in $O$46. The data validation shows a description, but he wants to store a code. I created a named range on sheet "Data Validation" with the codes in the first column and the descriptions in the second and set the data validation to the range. Because the descriptions are so long, I used code from Contextures.com to create a combobox that would come up and then go away. I altered the code to programmatically set the width of the combobox to the width of the named range. All of this was working and now is not. I don't know why. Before it was taking the multicolumn range as the data validation source, and now it says it needs a single column range. The code just doesn't seem to be very robust, but I'm sure I screwed it up somewhere. If you could talke a look at the code and the spreadsheet (I used row 46 for my testing), I would much appreciate it.

The second area regards checkboxes, only they are actually cells--not form controls. He wants to be able to check them as if they were in a group; i.e., if one was checked, the other could not be. I read somewhere that vbaexpress has an article on mutually exclusive cells, but for some reason my computer isn't pulling up that site.

Any help anyone can contribute is much appreciated. I think this whole thing would be better in an actual form, but this is what he has to work with this.

--elizabeth baker
Option Explicit

' cboTemp_Change runs when selection is made in combobox
' resets combox properties to default
Private Sub cboTemp_Change()
    On Error GoTo errHandler
    
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    Set cboTemp = ws.OLEObjects("cboTemp")
   
    On Error Resume Next
    With cboTemp
        'clear and hide the combobox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
    End With
    On Error GoTo errHandler

exitHandler:
  Application.EnableEvents = True
  Application.ScreenUpdating = True
      Exit Sub
errHandler:
  Resume exitHandler

End Sub

Private Function GetRangeWidth(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    
    Set ws = Sheets("Data Validation")
    Set rng = ws.Range(Target.Validation.Formula1)
    GetRangeWidth = rng.width
    
End Function

' When doubleclicked, a filled ComboBox will appear if a cell has data validation set
' Target is the range address of the calling cell (the cell which was clicked)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        
    On Error GoTo errHandler
    
    Dim cboTemp As OLEObject
    Dim ws As Worksheet
    Dim rng As Range
    Dim wd As Double
    Dim str As String
    
    Set ws = ActiveSheet
    Set cboTemp = ws.OLEObjects("cboTemp")
    Cancel = True
   
    wd = GetRangeWidth(Target)
    On Error Resume Next
    'check to see if ComboBox is visible; if yes, reset properties to default
    With cboTemp
        'clear and hide the combobox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Value = ""
    End With
    On Error GoTo errHandler
    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        With cboTemp
          .Visible = True
          'sets left margin of combobox to left margin of target
          .Left = Target.Left
          'sets top margin of combox to top margin of target
          .Top = Target.Top
          'sets width of combox to width of data validation range
          .width = GetRangeWidth(Target)
          'sets height of combobox to height of target
          .Height = Target.Height
          'fills combobox with data from data validation range
          .ListFillRange = Target.Validation.Formula1
          'specifies Target as the cell in which to store the combobox selection
          .LinkedCell = Target.Address
        End With
        
        cboTemp.Activate
    End If
    
exitHandler:
  Application.EnableEvents = True
  Application.ScreenUpdating = True
          Exit Sub
errHandler:
  Resume exitHandler
    End Sub


'=====================================================
'  Worksheet_SelectionChange passes the argument Target,
'  which is the cell the pointer was over when clicked.
'  This code runs every time a different cell on the worksheet is selected
'  --NOT when a selection is made in a combobox.
'  Ordinarily, a line such as "If Target.Count > 1, The GoTo ExitHandler",
'  which tests the Target to see if it is more than one call, would be used and,
'  if the Target were more than one cell, the ExitHandler would be called.
'  However, since some of the Targets here are merged cells,
'  we are using Target(1,1), which refers to the first cell in the Target range.
'======================================================

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Dim cboTemp As OLEObject
Set ws = ActiveSheet
Set cboTemp = ws.OLEObjects("cboTemp")

On Error Resume Next
'resets cboTemp properties to default
If Target.Validation.Type = 3 Then
    Application.EnableEvents = False
    If cboTemp.Visible = True Then
        With cboTemp
            .Top = 10
            .Left = 10
            .ListFillRange = ""
            .LinkedCell = ""
            .Visible = False
            .Value = ""
        End With
    End If
End If

errHandler:
    Application.EnableEvents = True
    Exit Sub

End Sub

Open in new window

CR-3-TCSO-ver-3.xls
0
Comment
Question by:baker_eliz
[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
  • 4
  • 4
8 Comments
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26277716
Hi Elizabeth.  Replace all of the current code in the Front Page sheet with the following.  I provided one example of grouping two ranges for the Xcheck/uncheck functionality and a two column combobox that appears when range o46 is selected.
Have a nice day.   Tom
0
 
LVL 6

Accepted Solution

by:
TomSchreiner earned 2000 total points
ID: 26277718
Where's the code?  :)
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Select Case Target.Address
        Case Range("o46").MergeArea.Address
        
            With Range("o46").MergeArea
                cboTemp.Top = .Top
                cboTemp.Left = .Left
                cboTemp.Width = .Width
                cboTemp.Height = .Height
                cboTemp.ColumnCount = 2
                cboTemp.ListRows = 19
                cboTemp.ListFillRange = "lstRoadwaySystem"
                cboTemp.ListWidth = 485
                cboTemp.ColumnWidths = "35;450"
                cboTemp.Visible = True
                cboTemp.LinkedCell = "o46"
                cboTemp.DropDown
            End With
            
        Case Else
        
            cboTemp.Visible = False
            
            'check X pair example
            Select Case Target.Address
            
                Case Range("ba22").MergeArea.Address
            
                    Target = "X"
                    Range("ba24") = ""
            
                Case Range("ba24").MergeArea.Address
        
                    Target = "X"
                    Range("ba22") = ""
                    
            End Select
        
    End Select
End Sub

Open in new window

0
 

Author Comment

by:baker_eliz
ID: 26277869
Thanks, Tom, for replying.

I can't hardcode the combobox in like that. It has to work for other cells/ranges in the worksheet as well, all at differing widths, some merged, some not.

Same for the checkboxes. They'll have differing numbers of checkboxes in the groups and must be mutually exclusive.

There are four dense pages to this form, all with comboxes and checkbox groups. The code must be able to pass in arguments so it works for all of them.

--e
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26278252
Hi Elizabeth.  Sure.   I understand.  I was trying to convey a method.  There are probably a handful of approaches but my initial thought would be to used named ranges, check for an intersection, and then pass the range to a function that determines where and what to display.
For example, on sheet front page, select every cell that will be checked or x'ed and name the range "CheckRange".  Within this range, name each exclusive group.  When the user makes a selection that intersects CheckRange, we call a function that determines the group;  assigns x to the target;  clears all other cells in the group.  Every sheet in your workbook would call the same function.
Viewing  your workbook, I cannot determine exactly which cells are exclusive or belong to a group.  The yes/no structure is obvious and that is why I provided an example there.
If you want me to provide you with a more comprehensive, working example, I will need each group's range address defined for the check/uncheck, each range that will render a combobox, and the range that will populate the combobox, on each worksheet...
0
 

Author Comment

by:baker_eliz
ID: 26278619
Thanks, Tom. I understand. And I understand the method. I think the MergeArea property you mentioned may be very useful. I tried using named intersections (my first approach), but the dropdown box provided by data validation (i.e., the width of the Target) was not wide enough to display the selection choices. I need a combobox  to change the width of display, the number of columns shown, and the number of rows shown.

Or maybe it would be easier to just change the column width temporarily?

Maybe the intersection method would work better than the doubleclick event? I don't know. I have a lot of time invested in the present code and am reluctant to abandon it without knowing the other approach is a better, more robust one. Having said that, my current code does not seem too robust to me (even when it was working!).

For some reason my code works sometimes and doesn't others. Sometimes the data validation will take a multi-column named range and sometime it insists on a single column range. I just can't figure out the problem. In my test row, Row 46, there are four comboboxes; only the first one has a multi-column data validation list.  The cells are O46 (for 1. Rdwy Sys), BF46 (for 2. Rdwy Part), CU46 (for 3. Street Prefix), and GK46 (4. Street Suffix). I don't really need a combobox that changes widths for any but O46 in my test row, but I will need that for other comboboxes in the workbook.

I'm very confused and don't know what approach would work best.

Given your suggestions, I think I can figure out the checkboxes. I'lll work on that. But at this point,. the comboboxes are my top priority.

Thanks.
--e
0
 
LVL 6

Expert Comment

by:TomSchreiner
ID: 26278752
Hi Elizabeth.  The code I posted provides for columnwidths.  You can actually use the source ranges width to dynamically set the column(s) width(s) of your combobox.
"If you want me to provide you with a more comprehensive, working example, I will need each group's range address defined for the check/uncheck, each range that will render a combobox, and the range that will populate the combobox, on each worksheet..."
I'll gladly help you but if I am limited to helping you within the constraints of your current code and method, then my own creative suggestion are sort of null and void.
Tom
0
 

Author Comment

by:baker_eliz
ID: 26280337
Hi, Tom. In my original code, I did actually use the source range width to dynamically set the column width of the comboboxes. I just took another look at the code I posted, and many of my code lines are colored green (although not in my Excelcode--must be a copy and paste thing), which would lead you to think they are commented out, when they should not be. Only the lines with a single apostrophe in front are comments. I don't know if this is creating confusion or not.

I'm putting this on the backburner until tomorrow evening at the earliest. Don't have time to work on it tonight or tomorrow. Will get back to you with update soonest.

Thanks for all your help.
--e
0
 

Author Comment

by:baker_eliz
ID: 26531065
Tom, sorry about the delay. My son and I decided to use a userform to collect the data and then store it in the spreadsheet. This is working well with much less code. How do I close out this question?

Thanks for your help.

--elizabeth
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

764 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