MaterialsDataBase

Dim criterion
Dim Name
Dim Update As Integer
Dim mpath As String
Dim lcolor As LongPtr
Dim FullColourCode As String
Dim RGBRed As Integer
Dim RGBGreen As Integer
Dim RGBBlue As Integer
Dim r As Integer


Private Sub UserForm_Initialize()
    'Initialize comboboxes
    InitializeCombos
    'Populate listbox
    PopulateList
    'Set default selections
    SetDefaultSelections
End Sub

'----Cursor position----------------------------------------------------

ColourTextBox.SetFocus
  
'---Empty TextBox-------------------------------------------------------
ColourTextBox.Value = ""
ManufacturerTextBox.Value = ""
ContactTextBox.Value = ""
OfficeTextBox.Value = ""
MobileTextBox.Value = ""
EmailTextBox.Value = ""
KeynoteTextBox.Value = ""
DescriptionTextBox.Value = ""
SubstraightTextBox.Value = ""
FormatTextBox.Value = ""
ThicknessTextBox.Value = ""
FinishTextBox.Value = ""
RGBTextBox.Value = ""
SearchTextBox.Value = ""

'---ListBox------------------------------------------------------------

Call Refresh_data



'---Empty ComboBox----------------------------------------------------

MaterialTypeComboBox.Clear
RGBComboBox.Clear
SearchComboBox.Clear

'---Empty Images------------------------------------------------------

RGBImage.BackColor = &H8000000F
BMPImage.Picture = LoadPicture(vbNullString)

'---Material Type combo box startup----------------------------------------------
Dim shH As Worksheet
Set shH = ThisWorkbook.Sheets("Hatch")
Dim lastRowshH As LongPtr
Dim lastColshH As LongPtr
lastRowshH = shH.Cells(shH.Rows.Count, "A").End(xlUp).row
lastColshH = shH.Cells(1, shH.Columns.Count).End(xlToLeft).Column

MaterialTypeComboBox.List = shH.Range("A2:A" & lastRowshH).Value

'---search combo box startup----------------------------------------------
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("MaterialsSheet")
Dim lastRowsh As LongPtr
Dim lastColsh As LongPtr
lastRowsh = sh.Cells(sh.Rows.Count, "A").End(xlUp).row
lastColsh = sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column

'SearchComboBox.AddItem sh.Cells(1, lastColsh).Value
'Check if column count is greater than 1 before adding items to combo box
If lastColsh > 1 Then
Dim c As Integer


For c = 2 To Application.WorksheetFunction.CountA(sh.Range("1:1"))
    Me.SearchComboBox.AddItem sh.Cells(1, c).Value
    
Next c
End If
'---RGB combo box startup----------------------------------------------

Dim shRGB As Worksheet
Dim lastRowRGB As LongPtr
Dim lastColRGB As LongPtr
Set shRGB = ThisWorkbook.Sheets("RGB")
lastRowRGB = shRGB.Cells(shRGB.Rows.Count, "A").End(xlUp).row
lastColRGB = shRGB.Cells(1, shRGB.Columns.Count).End(xlToLeft).Column

RGBComboBox.List = shRGB.Range("A1:I" & lastRowRGB).Value
RGBComboBox.ColumnCount = 9
RGBComboBox.ColumnWidths = "150,0,0,70,50,0,0,0,0"

'---Scroll----------------------------------------------------------------



End Sub


'---Add RGB Colour to RGB Sheet----------------------------------------

Private Sub AddChangeButton_Click()

Dim NewColour As LongPtr

If lcolor = xlNone Then
Exit Sub
End If

'---Make MaterialsSheet active-----------------------------------------

Sheet5.Activate

'---Determine emptyRow-------------------------------------------------

NewColour = WorksheetFunction.CountA(Range("A:A")) + 1

'---Transfer information-----------------------------------------------

Cells(NewColour, 1).Value = RGBTextBox.Value
Cells(NewColour, 2).Value = "Colour"
'Cells(NewColour, 3).Value = RGBTextBox.to other colour type
Cells(NewColour, 4).Value = FullColourCode
'Cells(NewColour, 5).Value = Shade of colour eg Blue
Cells(NewColour, 9).Interior.Color = RGB(RGBRed, RGBGreen, RGBBlue)

Call RGB_Refresh

End Sub

'---Open explorer Add Image---------------------------------------------

Private Sub AddImageButton_Click()

Application.FileDialog(msoFileDialogOpen).Show
On Error Resume Next
mpath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
BMPImage.Picture = LoadPicture(mpath)
BMPImage.PictureSizeMode = fmPictureSizeModeStretch



'If Dir(img) <> "" Then
    'Me.BMPImage.Vale = img
    'Me.BMPImage.Picture = LoadPicture(img)
'End If
End Sub

'---Open RGB Colour Palette ---------------------------------------------

Private Sub AddRGBButton_Click()

If Application.Dialogs(xlDialogEditColor).Show(10, 0, 125, 125) = True Then
lcolor = ActiveWorkbook.Colors(10)
Else
lcolor = xlNone
End If

    If Workbooks.Count > 1 Then
        Windows("Materials List Master Userform.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
'---Get the RGB value for each color (possible values 0 - 255)-----------

RGBRed = (lcolor Mod 256)
RGBGreen = (lcolor \ 256) Mod 256
RGBBlue = (lcolor \ 65536) Mod 256
FullColourCode = (RGBRed & "," & RGBGreen & "," & RGBBlue)
On Error Resume Next
RGBImage.BackColor = RGB(RGBRed, RGBGreen, RGBBlue)

End Sub

'---Colour Text box searches whilst entering name-----------------------

Private Sub ColourTextBox_Change()

'------Enter colour name------------------------------------------------

On Error Resume Next
If Me.ColourTextBox.Text = "" Then
Me.WorksheetListBox.Clear
Call Refresh_data
Exit Sub
End If

'---There is a reset issue with searching--------------------------------

Me.WorksheetListBox.Clear
Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("MaterialsSheet")
    Dim lastRow As Integer
    Dim lastCol As LongPtr
    Dim r As Integer
    lastRow = sh.Cells(Rows.Count, "A").End(xlUp).row
    lastCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column
    
    For r = 2 To lastRow
        a = Len(Me.ColourTextBox.Text)
        If UCase(Left(Sheet1.Cells(r, "B").Value, a)) = UCase(Me.ColourTextBox.Text) Then
        With Me.WorksheetListBox
    
        .AddItem Sheet1.Cells(r, "A").Value
        .List(.ListCount - 1, 1) = Sheet1.Cells(r, "B").Value
        .List(.ListCount - 1, 2) = Sheet1.Cells(r, "C").Value
        .List(.ListCount - 1, 3) = Sheet1.Cells(r, "D").Value
        .List(.ListCount - 1, 4) = Sheet1.Cells(r, "E").Value
        .List(.ListCount - 1, 5) = Sheet1.Cells(r, "F").Value
        .List(.ListCount - 1, 6) = Sheet1.Cells(r, "G").Value
        .List(.ListCount - 1, 7) = Sheet1.Cells(r, "H").Value
        .List(.ListCount - 1, 8) = Sheet1.Cells(r, "I").Value
        .List(.ListCount - 1, 9) = Sheet1.Cells(r, "J").Value
        .List(.ListCount - 1, 10) = Sheet1.Cells(r, "K").Value
        .List(.ListCount - 1, 11) = Sheet1.Cells(r, "L").Value
        .List(.ListCount - 1, 12) = Sheet1.Cells(r, "M").Value
        .List(.ListCount - 1, 13) = Sheet1.Cells(r, "N").Value
        .List(.ListCount - 1, 14) = Sheet1.Cells(r, "O").Value
        .List(.ListCount - 1, 15) = Sheet1.Cells(r, "P").Value
        .List(.ListCount - 1, 16) = Sheet1.Cells(r, "Q").Value
        .List(.ListCount - 1, 17) = Sheet1.Cells(r, "R").Value
        .List(.ListCount - 1, 18) = Sheet1.Cells(r, "S").Value
        .List(.ListCount - 1, 19) = Sheet1.Cells(r, "T").Value
        .List(.ListCount - 1, 20) = Sheet1.Cells(r, "U").Value
    End With
    End If
    Next
End Sub

'---DELETE-------------------------------------------------------------

Private Sub DeleteButton_Click()

If Me.ColourTextBox.Text = "" Then
Exit Sub
End If

If r = 0 Then
    MsgBox "Please select a colour", vbOKOnly + vbInformation
    Exit Sub
Else

    Dim answer As Integer
    RC = Sheet1.Cells(r, 2).Value
    answer = MsgBox("Are you sure you want to delete " & RC, vbQuestion + vbYesNo + vbQuestion, "Message Box Title")

    If answer = vbYes Then
    Rows(r).EntireRow.Delete
    End If


'---ListBox Refresh-------------------------------------------------
    Call Refresh_data
End If
r = 0
End Sub

'---Criteria To search-------------------------------------------------

Private Sub SearchComboBox_Change()


'If Me.ColourTextBox.Text = "" Then

    Dim c As Integer
    Dim column_headers
    column_headers = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U")
    
    For c = 1 To 21
        If Sheet1.Cells(1, c).Value = Me.SearchComboBox.Value Then
            criterion = column_headers(c - 1)
        End If
    Next
    

    Me.WorksheetListBox.Clear
    Me.WorksheetListBox.Value = ""
    'Me.WorksheetListBox.SetFocus
'End If
End Sub

'--Search-----------------------------------------------------------
Private Sub SearchTextBox_Change()



On Error Resume Next

If Me.ColourTextBox.Text <> "" Then
    Exit Sub
Else
    If Me.SearchTextBox.Text = "" Then
    Me.WorksheetListBox.Clear
    Call Refresh_data
    Exit Sub
    End If
'--There is a reset issue with searching-------------
    Me.WorksheetListBox.Clear
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("MaterialsSheet")
    Dim lastRow As Integer
    Dim lastCol As LongPtr
    Dim r As Integer
    lastRow = sh.Cells(Rows.Count, "A").End(xlUp).row
    lastCol = sh.Cells(1, Columns.Count).End(xlToLeft).Column
    
    
    'last_row = Sheet1.Range("A100000").End(xlUp).row
    For r = 2 To lastRow
        a = Len(Me.SearchTextBox.Text)
        If UCase(Left(Sheet1.Cells(r, criterion).Value, a)) = UCase(Me.SearchTextBox.Text) Then
        With Me.WorksheetListBox
    
        .AddItem Sheet1.Cells(r, "A").Value
        .List(.ListCount - 1, 1) = Sheet1.Cells(r, "B").Value
        .List(.ListCount - 1, 2) = Sheet1.Cells(r, "C").Value
        .List(.ListCount - 1, 3) = Sheet1.Cells(r, "D").Value
        .List(.ListCount - 1, 4) = Sheet1.Cells(r, "E").Value
        .List(.ListCount - 1, 5) = Sheet1.Cells(r, "F").Value
        .List(.ListCount - 1, 6) = Sheet1.Cells(r, "G").Value
        .List(.ListCount - 1, 7) = Sheet1.Cells(r, "H").Value
        .List(.ListCount - 1, 8) = Sheet1.Cells(r, "I").Value
        .List(.ListCount - 1, 9) = Sheet1.Cells(r, "J").Value
        .List(.ListCount - 1, 10) = Sheet1.Cells(r, "K").Value
        .List(.ListCount - 1, 11) = Sheet1.Cells(r, "L").Value
        .List(.ListCount - 1, 12) = Sheet1.Cells(r, "M").Value
        .List(.ListCount - 1, 13) = Sheet1.Cells(r, "N").Value
        .List(.ListCount - 1, 14) = Sheet1.Cells(r, "O").Value
        .List(.ListCount - 1, 15) = Sheet1.Cells(r, "P").Value
        .List(.ListCount - 1, 16) = Sheet1.Cells(r, "Q").Value
        .List(.ListCount - 1, 17) = Sheet1.Cells(r, "R").Value
        .List(.ListCount - 1, 18) = Sheet1.Cells(r, "S").Value
        .List(.ListCount - 1, 19) = Sheet1.Cells(r, "T").Value
        .List(.ListCount - 1, 20) = Sheet1.Cells(r, "U").Value
        End With
        End If
    Next
End If
End Sub

Private Sub UpdateButton_Click()

If Me.ColourTextBox.Text = "" Then
Exit Sub
End If

If r = 0 Then
    MsgBox "Please select a colour", vbOKOnly + vbInformation
    Exit Sub
Else
Dim Update As LongPtr

'Make MaterialsSheet active
Sheet1.Activate

'Determine emptyRow
Update = r

'Date stamp
strMyDate = Format(Now, "YYYYMMDDHHMMSS")

'Transfer information
Cells(Update, 1).Value = ("INT-" & strMyDate)
Cells(Update, 2).Value = ColourTextBox.Value
Cells(Update, 3).Value = ManufacturerTextBox.Value
Cells(Update, 4).Value = ContactTextBox.Value
Cells(Update, 5).Value = OfficeTextBox.Value
Cells(Update, 6).Value = MobileTextBox.Value
Cells(Update, 7).Value = EmailTextBox.Value
Cells(Update, 8).Value = ManufacturerTextBox.Value & Chr(10) & Chr(10) & ContactTextBox.Value & Chr(10) & OfficeTextBox.Value & Chr(10) & MobileTextBox.Value & Chr(10) & EmailTextBox.Value
Cells(Update, 9).Value = DescriptionTextBox.Value
Cells(Update, 10).Value = MaterialTypeComboBox.Value
Cells(Update, 11).Value = (class)
Cells(Update, 12).Value = SubstraightTextBox.Value
Cells(Update, 13).Value = FormatTextBox.Value
Cells(Update, 14).Value = ThicknessTextBox.Value
Cells(Update, 15).Value = FinishTextBox.Value
Cells(Update, 16).Value = MaterialTypeComboBox.Value & Chr(10) & "FORMAT: " & SubstraightTextBox.Value & " " & FormatTextBox.Value & Chr(10) & "THICKNESS: " & ThicknessTextBox.Value & "mm" & Chr(10) & "FINISH: " & FinishTextBox.Value
Cells(Update, 17).Value = KeynoteTextBox.Value
Cells(Update, 18).Value = RGBComboBox.Value

'---Get RGB Colour------------------------------------------------
RGBRed = (RGBImage.BackColor Mod 256)
RGBGreen = (RGBImage.BackColor \ 256) Mod 256
RGBBlue = (RGBImage.BackColor \ 65536) Mod 256

If RGBImage.BackColor = 2147483633 And RGBTextBox.Value = "" Then
Cells(Update, 19).Interior.Color = xlNone
Else
On Error Resume Next
Cells(Update, 19).Interior.Color = RGB(RGBRed, RGBGreen, RGBBlue)
End If


Dim RGBArr As Variant
RGBArr = Split(RGBTextBox.Value, " - ")
On Error Resume Next
rgb_name = RGBArr(0)
rgb_rgb = RGBArr(1)

Cells(Update, 19).Value = rgb_rgb
Cells(Update, 21).Value = mpath

Call Reset

'---ListBox Refresh-------------------------------------------------

Call Refresh_data
r = 0
End If
End Sub

Private Sub ShowCommandButton_Click()
Dim hold As Variant

hold = InputBox("Please input the password. ", "Hello!")

If hold <> "Tedup853" Then
MsgBox "Your password is wrong!", , "Hello"

Else
    If Workbooks.Count > 1 Then
        Windows("Materials List Master Userform.xlsm").Visible = True
    Else
        Application.Visible = True
    End If
End If
End Sub

Private Sub HideCommandButton_Click()
    If Workbooks.Count > 1 Then
        Windows("Materials List Master Userform.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
End Sub

Private Sub MaterialsDataBase_Terminate()

    If Workbooks.Count > 1 Then
        Windows("Materials List Master Userform.xlsm").Visible = True
    Else
        Application.Visible = True
    End If
End Sub
Private Sub ADDButton_Click()

If ColourTextBox.Value = "" Then
    MsgBox "Please create a colour", vbOKOnly + vbInformation
    Exit Sub
Else

    '"Materials List Master Userform.xlsm"
    Dim emptyRow As LongPtr

    'Make MaterialsSheet active
    Sheet1.Activate

    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

    'Date stamp
    strMyDate = Format(Now, "YYYYMMDDHHMMSS")

    '---look up class when selection made------------------------------------

    Dim class As Variant
    On Error Resume Next
    class = Application.VLookup(MaterialTypeComboBox.Value, Worksheets("Hatch").Range("A1:B1000"), 2, False)
  
    'Transfer information
    Cells(emptyRow, 1).Value = ("INT-" & strMyDate)
    Cells(emptyRow, 2).Value = ColourTextBox.Value
    Cells(emptyRow, 3).Value = ManufacturerTextBox.Value
    Cells(emptyRow, 4).Value = ContactTextBox.Value
    Cells(emptyRow, 5).Value = OfficeTextBox.Value
    Cells(emptyRow, 6).Value = MobileTextBox.Value
    Cells(emptyRow, 7).Value = EmailTextBox.Value
    Cells(emptyRow, 8).Value = ManufacturerTextBox.Value & Chr(10) & Chr(10) & ContactTextBox.Value & Chr(10) & OfficeTextBox.Value & Chr(10) & MobileTextBox.Value & Chr(10) & EmailTextBox.Value
    Cells(emptyRow, 9).Value = DescriptionTextBox.Value
    Cells(emptyRow, 10).Value = MaterialTypeComboBox.Value
    Cells(emptyRow, 11).Value = (class)
    Cells(emptyRow, 12).Value = SubstraightTextBox.Value
    Cells(emptyRow, 13).Value = FormatTextBox.Value
    Cells(emptyRow, 14).Value = ThicknessTextBox.Value
    Cells(emptyRow, 15).Value = FinishTextBox.Value
    Cells(emptyRow, 16).Value = MaterialTypeComboBox.Value & Chr(10) & "FORMAT: " & SubstraightTextBox.Value & " " & FormatTextBox.Value & Chr(10) & "THICKNESS: " & ThicknessTextBox.Value & "mm" & Chr(10) & "FINISH: " & FinishTextBox.Value
    Cells(emptyRow, 17).Value = KeynoteTextBox.Value
    Cells(emptyRow, 18).Value = RGBComboBox.Value

    If RGBImage.BackColor = -2147483633 And RGBTextBox.Value = "" Then
    Cells(emptyRow, 19).Interior.Color = xlNone
    Else
    Cells(emptyRow, 19).Interior.Color = RGB(RGBRed, RGBGreen, RGBBlue)
    End If

    Cells(emptyRow, 19).Value = FullColourCode

    '---Image Path-------------------------------------------------------
    Cells(emptyRow, 21).Value = mpath 'image file location.Value
    
    Call Reset

    '---ListBox Refresh-------------------------------------------------
    Call Refresh_data

End If
End Sub

'---Autofill-------------------------------------------------------------

Private Sub ManufacturerTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("MaterialsSheet")
Dim lastRow As LongPtr
lastRow = sh.Cells(Rows.Count, "A").End(xlUp).row
Manu = ManufacturerTextBox.Value
Dim FoundRow As LongPtr
    On Error Resume Next
    FoundRow = Application.WorksheetFunction.Match(Manu, sh.Range("C2:C" & lastRow), 0)
    On Error GoTo 0
If FoundRow = 0 Then
ContactTextBox.Value = ""
OfficeTextBox.Value = ""
MobileTextBox.Value = ""
EmailTextBox.Value = ""
Else
rautofill = FoundRow + 1 'adjust if headings make a difference
ContactTextBox.Value = Sheet1.Cells(rautofill, 4).Value
OfficeTextBox.Value = Sheet1.Cells(rautofill, 5).Value
MobileTextBox.Value = Sheet1.Cells(rautofill, 6).Value
EmailTextBox.Value = Sheet1.Cells(rautofill, 7).Value
End If
End Sub

Private Sub CLEARButton_Click()

Call Refresh_data
'Empty TextBox
ColourTextBox.Value = ""
ManufacturerTextBox.Value = ""
ContactTextBox.Value = ""
OfficeTextBox.Value = ""
MobileTextBox.Value = ""
EmailTextBox.Value = ""
KeynoteTextBox.Value = ""
DescriptionTextBox.Value = ""
SubstraightTextBox.Value = ""
FormatTextBox.Value = ""
ThicknessTextBox.Value = ""
FinishTextBox.Value = ""
RGBTextBox.Value = ""
SearchTextBox.Value = ""


'Empty ComboBox
MaterialTypeComboBox.Value = ""
RGBComboBox.Value = ""
SearchComboBox.Value = ""

'---Empty Images------------------------------------------------------

RGBImage.BackColor = &H8000000F
BMPImage.Picture = LoadPicture(vbNullString)

End Sub

Private Sub EXITButton_Click()

Dim answer As Integer

answer = MsgBox("Are you sure you want to Exit", vbQuestion + vbYesNo + vbQuestion, "Message Box Title")

If answer = vbYes Then

    Workbooks("Materials List Master Userform.xlsm").Close SaveChanges:=True
    Unload Me
    
    If Workbooks.Count > 1 Then
        Windows("Materials List Master Userform.xlsm").Visible = False
    Else
        Application.Visible = False
    End If
End If
End Sub


Sub Refresh_data()

'AssignRangeToArray

Dim MyArray() As Variant
Dim lastRow As LongPtr
lastRow = Cells(Rows.Count, "A").End(xlUp).row
MyArray = Sheets("MaterialsSheet").Range("A2:U" & lastRow).Value2

With WorksheetListBox
    .List = MyArray
    .ColumnCount = 21
    '.ColumnHeads = True
    '.ColumnWidths = "0,100,100,100,70,60,170,0,130,100,40,60,60,30,60,0,40,80,60,0,170"
    'colour manufacturer description material type substraight format thickness keynote rgbc rgb picture
    .ColumnWidths = "0,100,100,0,0,0,0,0,130,100,0,60,60,30,60,0,40,80,60,0,170"
End With

End Sub


'---RGB combo box Refresh----------------------------------------------

Sub RGB_Refresh()

Dim shRGB As Worksheet
Set shRGB = ThisWorkbook.Sheets("RGB")
Dim lastRowRGB As LongPtr
Dim lastColRGB As LongPtr
lastRowRGB = shRGB.Cells(Rows.Count, "A").End(xlUp).row
lastColRGB = shRGB.Cells(1, Columns.Count).End(xlToLeft).Column

With RGBComboBox
    .List = shRGB.Range("A1:I" & lastRowRGB).Value
    .ColumnCount = 9
    .ColumnWidths = "150,0,0,70,50,0,0,0,0"
End With
End Sub
Private Sub Reset()

'----Cursor position----------------------------------------------------

ColourTextBox.SetFocus
  
'---Empty TextBox-------------------------------------------------------
ColourTextBox.Value = ""
ManufacturerTextBox.Value = ""
ContactTextBox.Value = ""
OfficeTextBox.Value = ""
MobileTextBox.Value = ""
EmailTextBox.Value = ""
KeynoteTextBox.Value = ""
DescriptionTextBox.Value = ""
SubstraightTextBox.Value = ""
FormatTextBox.Value = ""
ThicknessTextBox.Value = ""
FinishTextBox.Value = ""
RGBTextBox.Value = ""
SearchTextBox.Value = ""

'---ListBox------------------------------------------------------------
Call Refresh_data
'Dim lastRow As Long
'Dim lastCol As Long
'lastRow = Cells(Rows.Count, "A").End(xlUp).row
'lastCol = Cells(1, Columns.Count).End(xlToLeft).Column

'With WorksheetListBox
    '.RowSource = "MaterialsSheet!A1:U" & lastRow
    '.ColumnCount = 21
    '.ColumnHeads = True
    '.ColumnWidths = "0,40,40,40,40,40,40,40,40,40,40,40,40,40,40,40,40,40,40,40,40"
'End With

'---Empty ComboBox----------------------------------------------------

MaterialTypeComboBox.Clear
RGBComboBox.Clear
SearchComboBox.Clear

'---Empty Images------------------------------------------------------

RGBImage.BackColor = &H8000000F
BMPImage.Picture = LoadPicture(vbNullString)

'---Material Type combo box startup----------------------------------------------
Dim shH As Worksheet
Set shH = ThisWorkbook.Sheets("Hatch")
Dim lastRowshH As LongPtr
Dim lastColshH As LongPtr
lastRowshH = shH.Cells(Rows.Count, "A").End(xlUp).row
lastColshH = shH.Cells(1, Columns.Count).End(xlToLeft).Column

MaterialTypeComboBox.List = shH.Range("A2:A" & lastRowshH).Value

'---search combo box startup----------------------------------------------
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("MaterialsSheet")
Dim lastRowsh As LongPtr
Dim lastColsh As LongPtr
lastRowsh = sh.Cells(Rows.Count, "A").End(xlUp).row
lastColsh = sh.Cells(1, Columns.Count).End(xlToLeft).Column

'SearchComboBox.AddItem sh.Cells(1, lastColsh).Value

Dim c As Integer


For c = 2 To Application.WorksheetFunction.CountA(sh.Range("1:1"))
    Me.SearchComboBox.AddItem sh.Cells(1, c).Value
    
Next c

'---RGB combo box startup----------------------------------------------

Dim shRGB As Worksheet
Dim lastRowRGB As LongPtr
Dim lastColRGB As LongPtr
Set shRGB = ThisWorkbook.Sheets("RGB")
lastRowRGB = shRGB.Cells(Rows.Count, "A").End(xlUp).row
lastColRGB = shRGB.Cells(1, Columns.Count).End(xlToLeft).Column

RGBComboBox.List = shRGB.Range("A1:I" & lastRowRGB).Value
RGBComboBox.ColumnCount = 9
RGBComboBox.ColumnWidths = "150,0,0,70,50,0,0,0,0"

End Sub


'---RGB Combo Box select----------------------------------------------------------

Private Sub RGBComboBox_Click()
Dim shRGB As Worksheet
Dim lastRowRGB As LongPtr
Dim lastColRGB As LongPtr
Dim colour As String
Set shRGB = ThisWorkbook.Sheets("RGB")
lastRowRGB = shRGB.Cells(Rows.Count, "A").End(xlUp).row
lastColRGB = shRGB.Cells(1, Columns.Count).End(xlToLeft).Column


Set RGBRange = shRGB.Range("A1:I" & lastRowRGB)
colour = RGBComboBox.Value
rgb_colour = Application.WorksheetFunction.VLookup(colour, RGBRange, 4, False)
RGBTextBox.Value = RGBComboBox.Value & " - " & rgb_colour

If rgb_colour = "" Then
    RGBImage.BackColor = &H8000000F
Else
    Dim arr As Variant
    arr = Split(rgb_colour, ",")
    rgb_red = arr(0)
    rgb_green = arr(1)
    rgb_blue = arr(2)
    RGBImage.BackColor = RGB(rgb_red, rgb_green, rgb_blue)
End If
End Sub

'Private Sub cmbMyList_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'HookListBoxScroll Me, Me.cmbMyList
'End Sub
'Private Sub lbxMyList_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'HookListBoxScroll Me, Me.lbxMyList
'End Sub

Private Sub WorksheetListBox_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
HookListBoxScroll Me, Me.WorksheetListBox
End Sub

Private Sub RGBComboBox_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
HookListBoxScroll Me, Me.RGBComboBox
End Sub

Private Sub SearchComboBox_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
HookListBoxScroll Me, Me.SearchComboBox
End Sub

Private Sub MaterialTypeComboBox_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
HookListBoxScroll Me, Me.MaterialTypeComboBox
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
UnhookListBoxScroll
End Sub

'-----Search Cloumn A to return Row---------------

Private Sub WorksheetListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Dim sPrompt As String
Dim sTitle As String
Dim lastRow As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("MaterialsSheet")
Dim r As Long
Dim rrow As String

lastRow = Cells(Rows.Count, "A").End(xlUp).row

If Me.WorksheetListBox.ListIndex > -1 Then
sTitle = "You selected..."
sPrompt = Me.WorksheetListBox.Value 'get the bound column
'Prompt = sPrompt & " AND " & r
'get the second column
'sPrompt = sPrompt & Me.WorksheetListBox.Column(1, Me.WorksheetListBox.ListIndex)
Else
sPrompt = "No selection"
End If

'MsgBox sPrompt, vbOKOnly, sTitle


rrow = sPrompt

Dim FoundRow As LongPtr
    On Error Resume Next
    FoundRow = Application.WorksheetFunction.Match(rrow, ws.Range("A1:A" & lastRow), 0)
    On Error GoTo 0
    
r = FoundRow

sTitle = "You selected..."
sPrompt = Me.WorksheetListBox.Value 'get the bound column
sPrompt = sPrompt & " AND ROW " & r & "(" & r - 1 & ")"

'MsgBox sPrompt

WorksheetListBox.Value = ""
ColourTextBox.Value = Sheet1.Cells(r, 2).Value
ManufacturerTextBox.Value = Sheet1.Cells(r, 3).Value
ContactTextBox.Value = Sheet1.Cells(r, 4).Value
OfficeTextBox.Value = Sheet1.Cells(r, 5).Value
MobileTextBox.Value = Sheet1.Cells(r, 6).Value
EmailTextBox.Value = Sheet1.Cells(r, 7).Value
DescriptionTextBox.Value = Sheet1.Cells(r, 9).Value
MaterialTypeComboBox = Sheet1.Cells(r, 10).Value
SubstraightTextBox.Value = Sheet1.Cells(r, 12).Value
FormatTextBox.Value = Sheet1.Cells(r, 13).Value
ThicknessTextBox.Value = Sheet1.Cells(r, 14).Value
FinishTextBox.Value = Sheet1.Cells(r, 15).Value
KeynoteTextBox.Value = Sheet1.Cells(r, 17).Value
RGBComboBox.Value = Sheet1.Cells(r, 18).Value

Dim RGBValue As Variant
RGBValue = Sheet1.Cells(r, 18).Value
If RGBValue = "" Then
RGBTextBox.Value = ""
Else
RGBTextBox.Value = (Sheet1.Cells(r, 18).Value) & " - " & (Sheet1.Cells(r, 19).Value)
End If

rgb_colour = Sheet1.Cells(r, 19).Value
If rgb_colour = "" Then
    RGBImage.BackColor = &H8000000F
Else
On Error Resume Next
    Dim arr As Variant
    arr = Split(rgb_colour, ",")
    rgb_red = arr(0)
    rgb_green = arr(1)
    rgb_blue = arr(2)
    RGBImage.BackColor = RGB(rgb_red, rgb_green, rgb_blue)
End If
imgsheet = Sheet1.Cells(r, 21).Value
If imgsheet = "" Then
    BMPImage.Picture = LoadPicture(vbNullString)
Else
    BMPImage.Picture = LoadPicture(imgsheet)
    BMPImage.PictureSizeMode = fmPictureSizeModeStretch
End If

End Sub