Expanding a Macro Already Written

[SanFelippo]

Hi,

I have gotten some new requests, and now need to know if it would be possible to add on to the Macro that was written for me in a previous post. Currently, the macro checks for the last row that has an address entered into it in column B, and then makes sure a "Type" is selected in that same row in column F. I was wondering if it would also be possible to expand this.

I would need the Macro to also check and make sure that there are selections made in columns Q, R, S, T, and U if there is an address populated in row B. Columns Q and R are cells that are manually filled in, so it would need to make sure they are not blank, while columns S, T, and U are dropdown lists, so it would need to make sure that none of those are still on the "(Select One)" option.

Would it also be possible to have the Macro call out what is missing then? For example, right now if it is missing Type, the macro calls out which row it is missing the Type in. the Column Names for Q - U are as follows:

Q: Total (Dwelling) Units
R: Multifamily Afforable Units
S: Construction Method
T: Manufactured Home Secured Property Type
U: Manufactured Home Land Property Interest

Here is the original post along with the Macro that was written to satisfy it:

To make this easier to understand, I am going to post some sample data below the question I have. I have two columns of data, one is a column with 15 rows that one would enter addresses into, and then the other is a column called type that has a drop list that contains 4 choices:

(Select One)
Type 1
Type 2
Type 3

What I am looking for is a macro I can run that will look and see if there is an address filled out in Column B, and if there is, go and look to see if a Type was chosen. If a Type was not chosen (AKA the Type is still on the choice "(Select One)", I would like a message box to appear that stating that if an address is filled out in Column B, then a corresponding Type must be chosen, If possible, could the message box also indicate which row the error occurred on?

I know this could be done with like 15 if, then statements, but I figure there has got to be an easier way to do it, so I figured I would ask. I am not looking for this to be run or coded in a Worksheet_Change(ByVal Target As Range) Sub. This will be something I attach to a button that will run it when pressed.

Any help?




So, if the situation below was present and I ran the Macro, I would get the error box popping us telling me that if cell B5 is populated with an address, cell F5 must have a Type Chosen.

Column (B) Address	Column (F) Type
123 Anywhere	Type 1
1234 Anywhere	Type 3
12345 Anywhere	Type 2
123456 Anywhere	(Select One)

<tbody>
</tbody>

*This code was written by a member named Rick, and excel MVP on this forum*

Sub CheckTypeSelected()
  Dim LastRow As Long, RowNums As String
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  RowNums = Replace(Application.Trim(Join(Application.Transpose(Evaluate(Replace("IF(LEN(B1:B#)*(F1:F#=""(Select One)""),ROW(B1:B#),"""")", "#", LastRow))))), " ", ", ")
  If Len(RowNums) Then
    MsgBox "If an address is filled out in Column B, then a corresponding Type must be chosen in Column F." & vbLf & vbLf & "These rows have no Type selected: " & RowNums
  End If
End Sub

 

[L. Howard]

With this statement??? "If there is an address filled out in Column B/Z,,,"

I read as if there is NO address filled out in either column, then the sheet is not being used and of course no action required.
If there is an (ONE ?) address filled in out in column B (Z will not be used until B is full) then the sum of % Residential Use (Column K/M) and % Retail Use must be 100%.

Perhaps I should have added, as stated, that means the sums should always be 100% in the K/M columns. No address entry then nothing is done on sheet, with AN address entry now evoke the 100% rules.

Howard

 

[SanFelippo]

With this statement??? "If there is an address filled out in Column B/Z,,,"

I read as if there is NO address filled out in either column, then the sheet is not being used and of course no action required.
If there is an (ONE ?) address filled in out in column B (Z will not be used until B is full) then the sum of % Residential Use (Column K/M) and % Retail Use must be 100%.

yup, essentially it means that if a row is being used, then whatever is put into column K and M in that row needs to sum to 100%.

 

[L. Howard]

Find the myCheck = MsgBox portion of the code assigned to the Next button and replace with this message box code, from the myCheck to End Sub

Howard

      myCheck = MsgBox("Check that columns K & M and/or columns AG & AH sum to 100%." & vbCr & _
                       "If so, then all other data point are positive." & vbCr & vbCr & _
                       "Unhide Sheets(Lists)?", vbExclamation + vbYesNo)
    
    If myCheck = vbNo Then
       '
      Else
     
        Sheets("(Lists)").Visible = True
        Sheets("(Lists)").Activate

    End If
   
  End If

End With

Sheets("4. Property Information").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub