-
Solved: events problems
i have a code, that runs every time cell J1 is selected, the problem is, it would also throw an error when any two or more cells would be selected. I figured I solved it by having excel just ignore the error, but it seems it's not that simple and if I select THE ENTIRE sheet, it just freezes excel :dunno .
How can I make it so that only when J1 is selected, my code is executed?
Here's my current code:
[VBA]Option Explicit
Dim cell As Range
Dim all_accounts() As Variant, i As Single
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target = Range("J1") Then
i = 0
ReDim all_accounts(0) As Variant
all_accounts(0) = "Customer Name"
'create list of accounts
frmSummary.boxAccountList.Clear
frmSummary.boxAccountList.AddItem "All Accounts"
For Each cell In Range("B:B")
If IsEmpty(cell) Then Exit For
If UBound(Filter(all_accounts, cell.Offset(0, 2))) = -1 Then
i = i + 1
ReDim Preserve all_accounts(0 To i) As Variant
all_accounts(i) = cell.Offset(0, 2)
'frmSummary.boxAccountList.Clear
frmSummary.boxAccountList.AddItem all_accounts(i)
End If
Next cell
'create month list
With frmSummary.boxMonth
.Clear
.AddItem "All Month"
.AddItem 1
.AddItem 2
.AddItem 3
.AddItem 4
.AddItem 5
.AddItem 6
.AddItem 7
.AddItem 8
.AddItem 9
.AddItem 10
.AddItem 11
.AddItem 12
End With
If ActiveCell = Range("J1") Then frmSummary.Show
End If
End Sub[/VBA]
Thanks.
-
mAYBE THIS WORKS
[VBA]
Option Explicit
Dim cell As Range
Dim all_accounts() As Variant, i As Single
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Me.Range("J1")) Is Nothing Then
If Target.Cells.Count = 1 Then
i = 0
ReDim all_accounts(0) As Variant
all_accounts(0) = "Customer Name"
'create list of accounts
frmSummary.boxAccountList.Clear
frmSummary.boxAccountList.AddItem "All Accounts"
For Each cell In Range("B:B")
If IsEmpty(cell) Then Exit For
If UBound(Filter(all_accounts, cell.Offset(0, 2))) = -1 Then
i = i + 1
ReDim Preserve all_accounts(0 To i) As Variant
all_accounts(i) = cell.Offset(0, 2)
'frmSummary.boxAccountList.Clear
frmSummary.boxAccountList.AddItem all_accounts(i)
End If
Next cell
'create month list
With frmSummary.boxMonth
.Clear
.AddItem "All Month"
.AddItem 1
.AddItem 2
.AddItem 3
.AddItem 4
.AddItem 5
.AddItem 6
.AddItem 7
.AddItem 8
.AddItem 9
.AddItem 10
.AddItem 11
.AddItem 12
End With
frmSummary.Show
End If
End If
End Sub
[/VBA]
-
