Enable/Disable multitab pages based on matrix

[A[L]C]

Hi all,

I have a userform which pops up when the user clicks on a certain cell. The userform has a multipage with 13 pages, each of which has a different vechicle type (eg Bulk Tipper, Chipliner, Container etc)

Now depending which material type (Compost, Green waste, Food waste etc) is selected, I need to disable/enable pages based on a matrix. Currently the matrix is formatted as vehicle type in the column, and material type along the top, with an 'x' in the cell if that vehicle can carry that material.

What I need to do is depending on which material type is selected on that row (ActiveCell.Offset(0, -2).Value), I need the form to enable or disable the pages which have the name of the vehicle.

Can anyone point me in the right direction please?

Thanks a lot in advance!
Paul


edit: have uploaded the file. hopefully it will make more sense now!

[A[L]C]

Hi all,

Ok, so Im having a go at this. Unfortunately the For each part of my code is not working. Could someone give me a pointer please/

Private Sub UserForm_Activate()
Application.ScreenUpdating = False

Dim lResult As Long, myRow As Range, Bcell As Range, Materialtype As String

Materialtype = ActiveCell.Offset(0, -2).Value
Truckname = ActiveCell.Value

Sheets("Vehicle-Material").Activate
Set myRow = Cells.Find(What:=Materialtype, After:=[A1], LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False)

lResult = myRow.Column
Set myRow = Nothing



   For Each Bcell In Range((Cells(2, lResult)) & ":" & (Cells(16, lResult)))
        'here I will check if there is an x, if not I will disable the page.
        If Bcell.Value = "x" Then
            MultiPage1.Pages(Truckname).Enabled = True
        Else
            MultiPage1.Pages(Truckname).Enabled = False
        End If
        
   Next Bcell
   
Application.ScreenUpdating = True

End Sub

[snb]

 
Private Sub UserForm_Activate()
  Application.ScreenUpdating = False

  with Sheets("Vehicle-Material")
    for each cl in .range("A2:A16").offset(,.cells.find(ActiveCell.Offset(0, -2).Value,,xlvalues,xlpart).column-1)
     MultiPage1.Pages(cells(cl.row,2).value).Enabled = cl.value="x"
    next
  end with
End Sub

[A[L]C]

Ok looks like Ive cracked it.

Private Sub UserForm_Initialize()
'Declate varibles - not all done yet
    Dim lResult As Long, myRow As Range, Bcell As Range, Materialtype As String, Truckname As String

    On Error GoTo errorhandler

    'set screen updating to false because I active vehicle-material to find - would like to not have to do this
    Application.ScreenUpdating = False

    'Set all pages to visible - there must be a better way to do this!
    Formpages = Me.MultiPage1.Pages.Count
    counter = 1
    Do Until counter = Formpages
        Me.MultiPage1.Pages(counter).Visible = True
        counter = counter + 1
    Loop

    'Determine what material type the have picked
    Materialtype = ActiveCell.Offset(0, -2).Value

    'Clean the string because named ranges dont have special characters
    Materialtype = Replace(Materialtype, " ", "")
    Materialtype = Replace(Materialtype, "-", "")

    'set the counter for the page number (because I cannot get it to work
    counter = 1

    For Each Bcell In Range(Materialtype)
        'incremented here as the data starts on row 2 (1 is the material header)
        counter = counter + 1

        'Clean the truckname as multipage names cannot contain spaces or &'s
        Truckname = Sheets("Vehicle-Material").Cells(counter, 2).Value
        Truckname = Replace(Truckname, " ", "")
        Truckname = Replace(Truckname, "-", "")
        Truckname = Replace(Truckname, "&", "")

        'here I will check if there is an x, if not I will disable the page.
        If Not Bcell.Value = "x" Then Me.MultiPage1.Pages(Truckname).Enabled = False

    Next Bcell

    Application.ScreenUpdating = True

errorhandler:
    Application.ScreenUpdating = True
    Exit Sub
End Sub

Nasty looking code though!