PDA

View Full Version : Solved: events problems



next
02-03-2012, 09:54 AM
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:
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

Thanks.

xld
02-03-2012, 10:39 AM
mAYBE THIS WORKS



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

next
02-03-2012, 10:49 AM
perfect, thanks!