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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

AndreasL

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,795
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
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
 

AndreasL

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,795
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
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
 

AndreasL

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

AndreasL

New Member
Joined
Dec 8, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
oh dear - sorry I am in middle of watching film at moment - will have to have a think later.

Dave
It's working now. Thanks you so much for your time. Enjoy the movie! :D
 

Forum statistics

Threads
1,181,182
Messages
5,928,582
Members
436,609
Latest member
manish1365

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
Top