Userform Textbox value to select named range based on Userform Combobox value

[AndreasL]

This is too specific for me to find any relevant info on the internet, at least from my searches, and I'm wondering if anyone can help out.

I've created a userform that is filled in by user. I would then like to use the values entered in the textboxes in this userform to populate a new lastrow in a named range in a worksheet.
There are several named ranges in this worksheet, and which one should be used is determined by a combobox in the userform. This combobox has a list with names corresponding to the different named ranges.
The first row in the named range has now value at the moment, but I could add a name here if need be.

Any takers? I can provide additional info as needed.

 

[AndreasL]

These are the codes currently in the userform:
(The named ranges I would like to place textboxvalues into is the ones defined by "cmbTYPE.list" and they should all go in defined columns in new lastrow.)

Private Sub UserForm_Initialize()
cmbHOLDBARHET.Value = SetFocus
cmbHOLDBARHET.List = Array("9 mnd", "12 mnd", "15 mnd")
cmbFORMAT.Value = SetFocus
cmbFORMAT.List = Array("F1", "F2", "F3", "F4", "F5", "F6", "F7", "F8", "F9", "F10", "F16")
cmbTYPE.Value = SetFocus
cmbTYPE.List = Array("Grandiosa", "BigOne", "Eksport", "Spesialbunn")
End Sub

Private Sub cmbFORMAT_Change()

    Dim ws As Worksheet
    Dim rFound As Range

    If Me.cmbFORMAT.ListIndex = -1 Then
        'Nothing selected, or user manually typed an invalid entry
        'Clear textboxes
        Me.tbSTREKK.Value = vbNullString
        Me.tbKRYMP.Value = vbNullString
        Me.tbANTALLDPAK.Value = vbNullString
        Me.tbANTALLTPAK.Value = vbNullString
        Me.tbKRYMPLENGDE.Value = vbNullString
        Me.tbDPAKSTREKK.Value = vbNullString
        Me.tbPALL.Value = vbNullString
        Me.tbKASSE.Value = vbNullString
    Else
        'Valid cmbFormat selected
        'Find it and populate the textboxes
        Set ws = ActiveWorkbook.Sheets("Sjaktler")
        Set rFound = ws.Range("Format").Find(Me.cmbFORMAT.Text, , xlValues, xlWhole)
        If Not rFound Is Nothing Then
            Me.tbSTREKK.Value = Intersect(ws.Range("Strekkfilm"), rFound.EntireRow).Value
            Me.tbKRYMP.Value = Intersect(ws.Range("Krympefilm"), rFound.EntireRow).Value
            Me.tbANTALLDPAK.Value = Intersect(ws.Range("Antall_F_pak_i_D_pak"), rFound.EntireRow).Value
            Me.tbANTALLTPAK.Value = Intersect(ws.Range("Antall_D_pak_pr_T_pak"), rFound.EntireRow).Value
            Me.tbKRYMPLENGDE.Value = Intersect(ws.Range("Lengde_krympefilm"), rFound.EntireRow).Value
            Me.tbDPAKSTREKK.Value = Format(Intersect(ws.Range("Strekkfilm_pr_F_pak__g"), rFound.EntireRow).Value, "#.00000")
            Me.tbPALL.Value = Intersect(ws.Range("Sjaktler_pr_pall"), rFound.EntireRow).Value
            Me.tbKASSE.Value = Intersect(ws.Range("Sjaktler_pr_kasse"), rFound.EntireRow).Value
        End If
    End If

End Sub

 

[AndreasL]

Anyone out there that can please help me with this?
It is the last piece in the puzzle to complete a big project I've been working on and modifying for the last couple of years.
First I made all functions in excel with vba, then put it over to a userform. Now I'm only missing this last piece to put the protection on and call it finished. (For now :P )

 

[dmt32]

Hi,
untested but maybe something like this will do what you want?

    Dim rng     As Range
    Dim NewRow  As Long

    On Error Resume Next
    Set rng = Range(cmbTYPE.Value)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub

    NewRow = rng.Row + rng.Rows.Count

Dave

 

[AndreasL]

Hi,
untested but maybe something like this will do what you want?

    Dim rng     As Range
    Dim NewRow  As Long

    On Error Resume Next
    Set rng = Range(cmbTYPE.Value)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub

    NewRow = rng.Row + rng.Rows.Count

Dave

Thanks for the response.

For testing purposes, how would I go about adding values from the textboxes to the chosen row? This is new territory for me. :P

 

[dmt32]

something like

    With Worksheets("Sheet1")
        .Cells(NewRow, 1) = Me.TextBox1.Value
        .Cells(NewRow, 2) = Me.TextBox2.Value
    End With

Dave

 

[AndreasL]

something like

    With Worksheets("Sheet1")
        .Cells(NewRow, 1) = Me.TextBox1.Value
        .Cells(NewRow, 2) = Me.TextBox2.Value
    End With

Dave

Ok, so it adds the value to the row below the named range. Problem is that that row is the header of the next named range.
Can it insert a new row at the end of the named range instead of using the other rows that's already there?
Again, thanks for helping me out here. It means a lot. <3

 

[AndreasL]

I would love for it to add a new row to the named range whilst keeping the formatting of the named range and any formulas in the rows of the named range above.
It might do this automatically I guess, but just mentioning it. It adds all textbox and combobox values correctly now, I just need to get a new row to place it in.
Feeling we're getting real close here.

 

[dmt32]

Can it insert a new row at the end of the named range instead of using the other rows that's already there?

 With Worksheets("Sheet1")
        .Cells(NewRow, 1).EntireRow.Insert xlFormatFromLeftOrAbove
        .Cells(NewRow, 1) = Me.TextBox1.Value
        .Cells(NewRow, 2) = Me.TextBox2.Value
    End With

Try adding line shown in bold to your code & see if does what you want

Dave

 

[AndreasL]

 With Worksheets("Sheet1")
        .Cells(NewRow, 1).EntireRow.Insert xlFormatFromLeftOrAbove
        .Cells(NewRow, 1) = Me.TextBox1.Value
        .Cells(NewRow, 2) = Me.TextBox2.Value
    End With

Try adding line shown in bold to your code & see if does what you want

Dave

It does! :D Thank you so much!
It doesn't transfer the formulas in certain columns tho. Any quickfix to make it bring those along?