J
jasonbates
Guys,
I've been using a pretty useful worksheet macro to create combo boxes
that work as list boxes... (it's useful because I can create wider
longer lists, and works on all of the validation drop downs on the
spreadsheet). Unfortunately after some heavy calculation macro's the
double click event doesn't seem to activate the macro again.
My question... what is the simplest code I need to add to my excel
workbook to stop this from happening... can I just define the combo box
object somewhere globally?
Secondly, is there an easy way to make this method work across the
workbook other than copying this macro into every sheet? I have a 100
pages I'd like to use this on, and I don't think that my Ctrl+c, ctrl+v
fingers could handle it. ;o)
I'm a neohpyte with excel VBA so please be gentle..... Thanks in
advance
JB>
--- the code ---
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
* * * *
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
I've been using a pretty useful worksheet macro to create combo boxes
that work as list boxes... (it's useful because I can create wider
longer lists, and works on all of the validation drop downs on the
spreadsheet). Unfortunately after some heavy calculation macro's the
double click event doesn't seem to activate the macro again.
collection.
My question... what is the simplest code I need to add to my excel
workbook to stop this from happening... can I just define the combo box
object somewhere globally?
Secondly, is there an easy way to make this method work across the
workbook other than copying this macro into every sheet? I have a 100
pages I'd like to use this on, and I don't think that my Ctrl+c, ctrl+v
fingers could handle it. ;o)
I'm a neohpyte with excel VBA so please be gentle..... Thanks in
advance
JB>
--- the code ---
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
* * * *
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
