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

AndreasL

New Member
Joined
Dec 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Glad suggestions helped & appreciate feedback

Dave
I edited after trying some. It isn't pulling the formulas from the named range.
Certain columns has formulas that I'd like for it to copy down.
You know any quick fix for this or is this more elaborate to do?
 
Upvote 0
Not tested but try following

VBA Code:
With Worksheets("Sheet1")
        .Rows(NewRow - 1).Copy
        .Rows(NewRow).EntireRow.Insert xlShiftDown
    End With
    Application.CutCopyMode = False

'rest of code

Dave
 
Upvote 0
Not tested but try following

VBA Code:
With Worksheets("Sheet1")
        .Rows(NewRow - 1).Copy
        .Rows(NewRow).EntireRow.Insert xlShiftDown
    End With
    Application.CutCopyMode = False

'rest of code

Dave
It seems all that changes is putting the new line in the second last row of the named range and not at the end. The formulas are not in the new row. :/
 
Upvote 0
It seems all that changes is putting the new line in the second last row of the named range and not at the end. The formulas are not in the new row. :/

oh dear - sorry I am in middle of watching film at moment - will have to have a think later.

Dave
 
Upvote 0
Rich (BB code):
 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
VBA Code:
    With Worksheets("Sjaktler")
        .Rows(NewRow - 1).Copy
        .Rows(NewRow).EntireRow.Insert xlShiftDown
    End With
    Application.CutCopyMode = False
    
    With Worksheets("Sjaktler")
        '.Cells(NewRow, 1).EntireRow.Insert xlFormatFromLeftOrAbove
        .Cells(NewRow, 1).Value = Me.tbVARENR.Value
        .Cells(NewRow, 2).Value = Me.cmbFORMAT.Value
        .Cells(NewRow, 3).Value = Me.tbNAVN.Value
        .Cells(NewRow, 5).Value = Me.tbDPAKVEKT.Value
        .Cells(NewRow, 7).Value = Me.tbARTNRSJAKTEL.Value
        .Cells(NewRow, 10).Value = Me.cmbHOLDBARHET.Value
        .Cells(NewRow, 11).Value = "NEI"
        .Cells(NewRow, 12).Value = Me.tbSTREKK.Value
        .Cells(NewRow, 13).Value = Me.tbKRYMP.Value
        .Cells(NewRow, 14).Value = Me.tbANTALLDPAK.Value
        .Cells(NewRow, 15).Value = Me.tbANTALLTPAK.Value
        .Cells(NewRow, 16).Value = Me.tbKRYMPLENGDE.Value * 1
        .Cells(NewRow, 17).Value = Me.tbFPAKSTREKK.Value * 1
        .Cells(NewRow, 18).Value = Me.tbPALL.Value
        .Cells(NewRow, 19).Value = Me.tbKASSE.Value

I still had the ".EntireRow.Insert xlFormatFromLeftOrAbove" line active. Seems to be working fine now. :D
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top