VBA: Finding a cell address

[musoguy]

Hi there. I am a little stuck partway through a VBA code I am writing.

I have a worksheet that has a list of staff members in Column A and a list of how many classes they currently teach in Column B. The values in both Columns are pulled from other worksheets using calculations, so there is no physical user input on this sheet at all.

What I want to happen is if a cell in Column B changes to the value 5, then to get hold of the cell address to use in a msgbox.

All I have come up with so far is:

Private Sub Worksheet_Calculate()

    Select Case Range("B1").Value
    
        Case 5

            MsgBox (Worksheets("Sheet1").Range("A1").Value) & " now has 5 classes.", vbExclamation, "WARNING"


    End Select

Which works fine, but I have 100 rows of data, and presumably if I write 100 Select Cases it will slow the workbook down. Plus, when I added a second Select Case the Msgboxs went crazy! I thought a Worksheet_Calculate event only happened if a formula result changed but it started firing a msgbox every time I came out of a cell anywhere in the workbook. Stumped (but trying!)

James

 

[musoguy]

Thanks again Rob. Unfortunately when I enter the code, nothing happens. Am I meant to create the helper cell and column manually, or does the code do it?

 

[Ralajer]

It is all automatic if everything is running correctly. Toggle a breakpoint in the code near the top, before the first if statement, and it should pause there that way you know it is running.

Just to be sure you put the code in the worksheet code module containing the count data and not a standard module or workbook code module. If it is in the right place it will run whenever the sheet is recalculated.

 

[musoguy]

It is. Strange. I'll look into it more tomorrow, and let ya know if it works for me Thanks again

 

[musoguy]

Hi Rob, the problem was to do with the row the data started on as it was row 3 not 2 (which I hadn't stated), once I figured that out, everything was great. Thank you so much for helping me out

 

[musoguy]

I've hit one more issue with what I am trying to do with the great code I was given above by Rob. Due to some changes when creating the worksheet, it is now possible for a person's name to appear in Column A more than once, which means the amount of classes they teach also appears in Column D more than once. The problem is if I create a private sub to automatically remove duplicates in Column A, it deletes all values in the column and pastes in it's place the unique values only. This would happen every time I add a person's name to another sheet. Because of this, there is a moment when the amount of classes everyone teaches becomes 0 (when their names disappear) and then all the numbers come back when the names reappear. This triggers the code you wrote to bring up msgboxes for every person who has 5 or more classes so far.

I can get round it by not removing the duplicates, so new additions are just added to the end of the list in Column A, but doing this makes the msgbox appear as many times as their name appears when the count reaches 5. For example, if their name appears 3 times in the list, I will receive 3 message boxes for them.

I understand why this is happening, but I can't think of a solution. Is there any way that if a name is duplicated, to only bring up one msgbox? Or if easier, is there a way to remove the duplicates only, without deleting everything in the Column?

I hope this makes sense, and someone can help

James

 

[Ralajer]

What code are you using to delete the duplicates? VBA Express has a DeleteDups subroutine that shouldn't blank your columns. It starts at the bottom of column A and deletes the entire row if the value appears more than once.

Private Sub Worksheet_Change(ByVal Target As Range)
    '// Call DeleteDups if change is in Column A (1)
    If Target.Column = 1 Then DeleteDups
End Sub

This should be called each time you change/add an item in column A and before the Worksheet_Calculate event.

The Sub Worksheet_Calculate() code can also be changed but I think it probably better to delete the duplicates unless there is a reason to keep them.

 

[musoguy]

Sorry Rob, I got so caught up in what I was doing I forgot to thank you for your help. Unfortunately the method you showed me didn't work for my situation as I didn't want to delete any rows, only the cell values. However I finally figured out a way to get it done by rethinking the spreadsheet as a whole. Thanks again for helping me on my quest!

James