How to display msg box when range of cells is populated

[BrianAlmond]

I would like a msg box to be displayed after the entirety of several ranges of data have been filled in, this includes "zeros" as the data will be used in equations.

Right now, I'm able to display the message I need only when the "last" cell has data entered, but I want to make this idiot-proof in case that particular cell is not the last cell to have data entered into it.

I'm currently using:

Private Sub Worksheet_Change(ByVal Target As Range)
      
      If Target.Address = "$D$93" Then
        Run "DailyCapacityOutput"
    
    End If
        
End Sub

How can this be modified to incorporate multiple ranges of data?

THANKS!

-Brian

 

[lenze]

Hi and welcome to the board!!!!
Can you specify the ranges you require? Are they on a single row, or spread out across the sheet?? Yo may be able to use Data Validation!! are the entries numeric? Text? Both?
lenze

 

[BrianAlmond]

Thanks for the response!

All of the entries will be numeric. They are also all in the same column, there are a few cells though I would like to not select. The current ranges are D3:D51, D54:D80, D83:D90, and D93.

What is data validation and how does it work?! I apologize for my lack of knowledge, I have a total of about 4 days experience in VBA under my belt...

Thanks again,
Brian

 

[lenze]

You can use DV to prohibit an entry in D93 until all other cells are filled in. Question: Is a blank cell considered a zero, or does the user physically enter a zero?

lenze

 

[BrianAlmond]

OK, I understand the principle of how DV would work. The user should have to physically enter zeros.

 

[BrianAlmond]

Would I be able to have a separate msg box that would notify the user to "complete filling out the range of data before entering the data here" in case they were to try to enter a number into that cell before the others...?

 

[lenze]

In DV on cell D93, choose Custom. For formula, enter

=COUNT(D3:D51,D54:D80,D83:D90)=84

Use the warning tab in DV to display the message.
lenze

 

[BrianAlmond]

Thanks Lenze, that is definitely the effect I'm attempting. The only issue I run into now is the calculation still performs and pops up the msg box. I'm really trying to get that message to be dependent on whether the range of cells has had data entered before that calculation is made. I'll keep playing with it, I appreciate all the help!

 

[lenze]

Can you explain exactly howyou set it up?/ if done correcrtly, you willnot be able to enter anything in D93 if there are any blank cells in your range. Is D93 a formula??

lenze

 

[BrianAlmond]

Well the way its set up now is D93 is the "last" cell that is filled in with data that will be used in a calculation. I do not want the calculation to take place until all cells within the data range have been populated. The data to be entered is numeric.

Currently in cell D93, I have data validation set up just as you suggested - using a custom format with the formula: =COUNT(D3:D51, D54:D80, D83:D90)=84

The way its been acting now is when I enter all the data and for example I leave one cell in the range blank; after entering the data into D93, my warning message pops up and says "Fill in all cells before continuing" with the options to push yes, no, or cancel. If I press yes, the message box pops up with the displayed calculation - I don't want that to happen until all the data is filled in. If I press no, I'm stuck in a loop in that cell and it continues to pop up the warning message from the data validation. If I press cancel, the calculation message box displays and I have to push "OK" from 2 - 13 times before the message box stops popping up.