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

 

[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

 

[Norie]

Paul

How are the different trucks selected?

Or is it the type of waste that's selected and the matrix determines which trucks are suitable and their tab is enabled?

 

[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!

 

[A[L]C]

Paul

How are the different trucks selected?

Or is it the type of waste that's selected and the matrix determines which trucks are suitable and their tab is enabled?

The second. Certain trucks can carry certain materials. So depending on which material we need to make that page enabled/disabled.

 

[Norie]

So how is the waste type selected?

You seem to be getting the value from a cell on a worksheet?

PS If you set all the tabs visible in design mode then you shouldn't need that loop to show them all in the initilaize event.

Also you don't need really need a counter, if you want to loop through all the Pages in a multipage:

Dim pg As MSForms.Page

For Each pg In Me.MultiPage.Pages
      pg.Visible = True
Next pg