Excel VBA help with Worksheet Change function

[jlancton]

Good Morning,

I wrote the (not elegant) code below to make sure the correct data was entered or not entered in cells based on other cells. I used vba because we could only go so far with data validation and the Indirect function.

There are two problems. First, the code is slow, probably because of the many if/then's. Second, it only works for one row, and I need it for two. I tried copying and pasting, and changing the cell references, but it just got stuck in an endless loop.

I'm sure there must be a more concise and therefore faster way to accomplish this in code, as well as make it work for rows 39 AND 40.

Any help will be greatly appreciated.

Thank you,

-Jeff

Code:

Public Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim check As Boolean
Dim response
Dim somers As Boolean
Dim unit As Boolean

If Range("F39").Value = "" Then
Range("D39:e39").Value = ""
GoTo endsub
Else
If Range("f39") = "SMNH" Then
somers = True
Else: somers = False
End If
If Range("d39").Value = "" Then
unit = True
Else
unit = False
End If
If somers And unit = True Then
check = True
Else
check = False
End If
If check = True Then
response = MsgBox("Please Select A Unit", vbOKCancel, "Unit Not Selected")
If response = vbOK Then
Range("d39").Select
GoTo endsub
Else
Range("f39:g39").Value = ""
GoTo endsub
End If
Else
Range("f39").Select
End If
End If
If somers = False Then
If unit = False Then
Range("d39").Value = ""
End If
End If

endsub:
End Sub

 

[SkipVought]

Jeff,

I have not looked closely at your code, but it was obvious that you are CHANGEING values on the sheet in this routine.

Guess what???

When your routine makes a change, it FIRES the routine again! That's what's know as a RECURSIVE call -- ad infinitum!

Using the Worksheet_Change event to CHANGE the sheet can be VERY TRICKY!. It can be done, but it takes some advances programming skills, the do the following...

1) lock out the worksheet_change event process once it has started and

2) assure ABSOLUTELY, that the process runs to completion without interruption.

I'd suggest that instead, you make a VALIDATION routine that runs on some other event.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jlancton]

Hi Skip,

Thanks for the reply. I understand what you mean about the infinite looping, yet it does work without looping in this case. We had another Excel file that used the worksheet change and changed values inside of it, using application.enableevents which I forgot to put in what I wrote. Still, if there's a better way I'm open to it.

Could use the selection change event to call the same code?

-Jeff

 

[SkipVought]

Using EnableEvents, does 1) above if you set EnableEvents FALSE at the beginning and TRUE at the end.

You also have to assure 2) above as well or EnabelEvents will never be set TRUE and any following events will never be detected until you close & open the workbook.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884