We help IT Professionals succeed at work.
Get Started

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

955 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
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE