Validation lists needed to autopopulate, or not

[Monsignor]

The goal is for efficiency.
The user wants to have the options to:

1. Select component-by-component or,
2. Select a kit.

In 7 of 10 times, a Kit is going to be ordered.

My thought is to add a dropdown list in Column F that offers:
- Component
- Kit A
- Kit B
- Kit C


If "Component" is chosen, the user will manually select components in Columns G:J
If a Kit is chosen, Excel will automatically populate G:J based on the predetermined kit components.


At a higher level (I think I can handle this part) there needs to be a warning if someone selects a kit, then changes a component. No! That would be a Component order NOT a Kit order.


Note: I'm primarily a Formula guy. VBA is still something a bit foreign to me. I've used it but prefer worksheet functions. I would even prefer recording a macro before VBA. But I suspect this is going to call for at least a little VBA.

What are some thoughts on this puzzle?

 

[Monsignor]

BTW, here is the code that I've used. Subtle changes. E.g., your code used 'component' My lists use 'components.' That's why I was getting some #N/A in some cells. With that small change, all was right with the world.

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Intersect(Target, Columns("H:N")) Is Nothing) Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False
 
    If Not (Intersect(Target, Columns("H:H")) Is Nothing) Then
        If Target.Value = "COMPONENTS" Or _
                Target.Value = vbNullString Then 'Clear row and use dropdowns
            Target.Offset(0, 1).ClearContents
            Target.Offset(0, 3).Resize(1, 4).ClearContents
        Else 'Use Vlookups
            Target.Offset(0, 1).FormulaR1C1 = _
                "=VLOOKUP(RC8,'Dep Lists and Kits'!C1:C7,COLUMN(RC[-7]),FALSE)"
            Target.Offset(0, 3).Resize(1, 4).FormulaR1C1 = _
                "=VLOOKUP(RC8,'Dep Lists and Kits'!C1:C7,COLUMN(RC[-7]),FALSE)"
        End If
    Else 'Target in Columns I:N
        If Cells(Target.Row, 8).Value = vbNullString Then
            Cells(Target.Row, 8).Value = "COMPONENTS"
        Else
            If Cells(Target.Row, 8).Value <> "COMPONENTS" Then
                Target.FormulaR1C1 = _
                    "=VLOOKUP(RC8,'Dep Lists and Kits'!C1:C7,COLUMN(RC[-7]),FALSE)"
                MsgBox "Can't edit parts of a kit!", vbExclamation, "Error"
            End If
        End If
    End If
CleanUp:
    Application.EnableEvents = True
End Sub

 

[Monsignor]

That sequence shouldn't cause that result if you have the same Columns as my example. The "Can't edit parts of a kit." message only occurs in response to a change to Columns I:N (when H is not equal to "Component". You should be able to change Column H repeatedly without triggering that message. Perhaps in adjusting the code for your layout your columns are aligned slightly differently?

Yes, I think the difference here was in
Component vs. Components

All fixed!

 

[Jerry Sullivan]

Yes, I think the difference here was in
Component vs. Components

All fixed!

Nice detective work...so often it's the details that get 'ya.

 

[Monsignor]

Final tweak

Looking great except for the activity in columns J and K.

Column K is just an "x" to represent 1x4, 2x8, etc. This isn't any big deal because I can add the "x" into the source data section.

The challenge is with K.
I see your previous note about an assumption that there is Data Validation in each cell in the range.

Currently, there is no validation in J or K. I may, later include validation in K to restrict it to a whole number. But right now, there is none. Sorry that I missed this and I hope it's not a big deal. Otherwise, H, I, L, and M are fine.


I tried adjusting the code but didn't get it quite right.

Note in the pic, the upper image is perfect. When I go back and change H11 to blank (lower image), J11 errors out and in the formula bar the VLOOKUP formula is visible.

 

[Monsignor]

Detail:
The upper image is perfect because I typed in the 1. If I blank it out and go back to select a kit, J11 remains blank.

 

[Jerry Sullivan]

Note in the pic, the upper image is perfect. When I go back and change H11 to blank (lower image), J11 errors out and in the formula bar the VLOOKUP formula is visible.

I wasn't able to replicate that, and I don't think that should have happened under normal operating conditions. My guess is that during the process of your debugging efforts, somehow: Application.EnableEvents didn't get reset back to TRUE

If that happened, then when you blanked out H11, the Worksheet_Change event code would not have run and your Vlookup formulas would have remained instead of being cleared.

I'd suggest you make a Reset macro to allow you to enable events if that happens- although it shouldn't normally do that.

You can copy this into a Standard Code Module or your Personal.xlsb

Sub Reset_Enable_Events()
    Application.EnableEvents = True
End Sub

Even if that was the temporary problem, it would be a good idea to take your VLookup formulas one step further and add IFERROR to handle any Vlookup errors.

You could try the code below. I've also used Union() to build a single range reference for the formulas and clearcontents instead of calling it separately for the two ranges.

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Intersect(Target, Columns("H:N")) Is Nothing) Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    On Error GoTo CleanUp
    Application.EnableEvents = False
 
    If Not (Intersect(Target, Columns("H:H")) Is Nothing) Then
        If Target.Value = "COMPONENTS" Or _
                Target.Value = vbNullString Then 'Clear row and use dropdowns
           Union(Target.Offset(0, 1), Target.Offset(0, 3).Resize(1, 4)) _
                .ClearContents
        Else 'Use Vlookups
            Union(Target.Offset(0, 1), Target.Offset(0, 3).Resize(1, 4)).FormulaR1C1 = _
                "=IFERROR(VLOOKUP(RC8,'Dep Lists and Kits'!C1:C7,COLUMN(RC[-7]),FALSE),"""")"
        End If
    Else 'Target in Columns I:N
        If Cells(Target.Row, 8).Value = vbNullString Then
            Cells(Target.Row, 8).Value = "COMPONENTS"
        Else
            If Cells(Target.Row, 8).Value <> "COMPONENTS" Then
                Target.FormulaR1C1 = _
                    "=IFERROR(VLOOKUP(RC8,'Dep Lists and Kits'!C1:C7,COLUMN(RC[-7]),FALSE),"""")"
                MsgBox "Can't edit parts of a kit!", vbExclamation, "Error"
            End If
        End If
    End If
CleanUp:
    Application.EnableEvents = True
End Sub

 

[Monsignor]

Thanks again.

I'm about to try this solution. Quick reply. I had another spreadsheet open and it has a macro to toggle a macro on and off. I noticed that it impacted this spreadsheet. So, that may be what happened last night.

ONWARD!!