Worksheet_Change Event

[schang_825]

Hi all,

I have created a schedule for the tenants of a new condo to move in. They are assigned a date, time and elevator. One tab has the schedule in a calendar format with the unit numbers entered into the desired date/time/ elevator slot.

In another tab, I have the tenant's contact information and Moving Company info. When the tenant calls to schedule their move in time, I would like to enter their unit number on the calendar, then have the "Contact Info" tab open and display only that unit number and select the Moving Company cell.

I tried to use the "Worksheet_Change" event, but it doesn't seem to be working. This is what I have:

Private Sub Worksheet_Change(ByVal Sh As Object, ByVal Target As Range)
Dim rcell As Range

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error GoTo ErrHnd

If Not Application.Intersect(Range("D10:GC30"), Target) Is Nothing And Target.Text <> "" Then

Sheets("Contact Info").Select

Sheets("Contact Info").Range("B2:B500").EntireRow.Hidden = True

For Each rcell In Sheets("Contact Info").Range("B2:B500")

If rcell.Value = Target.Text Then
rcell.EntireRow.Hidden = False
rcell.Offset(0, 4).Select
End If
Next rcell

End If

Application.ScreenUpdating = True

ErrHnd:
Err.Clear
Application.EnableEvents = True

End Sub

Any suggestions??

 

[Jerry Sullivan]

Aloha,

Your procedure declaration line is incorrect. It should read....

Private Sub Worksheet_Change(ByVal Target As Range)

 

[schang_825]

Thanks for your reply! Oddly, that's what I had before and it didn't work. But it seems to now until it gets to this part:

For Each rcell In Sheets("Contact Info").Range("B2:B500")

If rcell.Value = Target.Text Then
rcell.EntireRow.Hidden = False
rcell.Offset(0, 4).Select
End If
Next rcell


It selects the "Contact Info" sheet and hides all the rows, but it does not find the unit number and show that row...

Any other suggestions.

Thanks!

 

[Jerry Sullivan]

It looks like that code should work; however rather that finding why that isn't working for you,
it would be much more efficient to use .Find instead of stepping through each room.

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rMatch As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error GoTo ErrHnd
    If Target.Cells.Count > 1 Then GoTo ErrHnd
    If Not Application.Intersect(Range("D10:GC30"), Target) _
        Is Nothing And Target.Text <> "" Then
        With Sheets("Contact Info")
            Set rMatch = .Range("B2:B500").Find(What:=Target.Text, _
                LookAt:=xlWhole, MatchCase:=False)
            If rMatch Is Nothing Then
                MsgBox "Match not found"
            Else
                .Activate
                .Range("B2:B500").EntireRow.Hidden = True
                rMatch.EntireRow.Hidden = False
                rMatch.Offset(0, 4).Select
            End If
        End With
    End If
ErrHnd:
    Err.Clear
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

 

[schang_825]

Thanks that worked great!!

 

[schang_825]

Hi again,

The code was working well and I added something else to it to check if the unit number entered is within a certain range and if it falls before a certain date. It works sometimes, and then all of a sudden, it stops working completely.

This is what I have:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rMatch As Range
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim ColLetter As String
Dim ColNum As Integer

On Error GoTo ErrHnd
If Target.Cells.Count > 1 Then GoTo ErrHnd
If Not Application.Intersect(Range("D10:GC30"), Target) _
Is Nothing And Target.Text <> "" Then

ColNum = Target.Column
ColLetter = Left(ActiveSheet.Cells(1, ColNum).Address(False, False), (ColNum <= 26) + 2)

If Range(ColLetter & "8").Value < Range("B100").Value And Target.Value > 600 And Target.Value < 1599 Then
MsgBox ("This unit closes after November 2nd")
Target = ""
End If

If Range(ColLetter & "8").Value < Range("B101").Value And Target.Value > 1600 And Target.Value < 2599 Then
MsgBox ("This unit closes after November 9th, dummy!")
Target = ""
End If

If Range(ColLetter & "8").Value < Range("B102").Value And Target.Value > 2600 And Target.Value < 3699 Then
MsgBox ("This unit closes after November 16th!")
Target = ""
End If

If Range(ColLetter & "8").Value < Range("B103").Value And Target.Value > 3700 And Target.Value < 4899 Then
MsgBox ("This unit closes after November 22nd!")
Target = ""
End If

With Sheets("Contact Info")
If Target.Text = "" Then Exit Sub Else:
Set rMatch = .Range("B2:B500").Find(What:=Target.Text, _
LookAt:=xlWhole, MatchCase:=False)
If rMatch Is Nothing Then
MsgBox "Invalid Unit!"
Else
.Activate
.Range("B2:B500").EntireRow.Hidden = True
rMatch.EntireRow.Hidden = False
rMatch.Offset(0, 4).Select
ActiveWindow.SmallScroll Down:=-114
End If
End With
End If
ErrHnd:
Err.Clear
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Any ideas as to why it only works sometimes??

Thanks!

 

[Jerry Sullivan]

It works sometimes, and then all of a sudden, it stops working completely.

On quick review, I think your problem is here:

If Target.Text = "" Then Exit Sub Else:

At the beginning of the code you set:

Application.EnableEvents = False

Typically that is being reset to =True at the end of your code,
however when Target.Text = "" your Exit Sub statement is jumping out before that happens.

You could revise to..

If Target.Text = "" Then GoTo ErrHnd

(no need for the Else)

I'm not sure that will fix everything, but it will be a start in the right direction.

 

[schang_825]

Thanks for the quick reply, but unfortunately it doesn't work. I enter a unit number, and nothing happens...

If I close and re-open the spreadsheet, it sometimes starts working again. But then suddenly stops. Not sure what is happening!

Any other suggestions??

 

[Jerry Sullivan]

On second quick review....

You are clearing Target.Text then trying to find a match for Target.Text here...

 Set rMatch = .Range("B2:B500").Find(What:=Target.Text, _
                LookAt:=xlWhole, MatchCase:=False)

 

[Jerry Sullivan]

I take that last comment back....
I see you are only doing the find if Target.Text has a value.

I'll mock this up so I can be more helpful.
What type of values are in B101:B103? Are those dates?