I have worksheet that I use for LEAN managment with risk management, etc. on the first sheet there is a user form that inputs data to the "database" sheet.
Database sheets has several columns of data and if i want to insert a column at the A1 column then it messes all my comboboxes and textboxes that refer
to the "database" sheet. The problem is I hardcoded all comboboxes and textboxes on the userform and now I have to add some more options on the user form that inputs data on the "database" sheet.
I searched on the internet and found that I could use "named ranges" instead so that I can insert new columns without messing up my comboboxes and textboxes. I tried expermenting with named ranges without sucess so maybe one of you guys can point me in the right direction.
Code:
Public Sub Save_Click()
Dim NextRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Databas")
Dim rg As Range
Set rg = Worksheets("Databas").Range("Risk")
' The statement simulates activating the last cell in column A, pressing End,
' pressing Up Arrow, and then moving down one row. If you do that manually,
' the cell pointer will be in the next empty cell in column A — even if the data
' area doesn’t begin in row 1 and contains blank rows.
NextRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
rg.NextRow.Value = Me.cboRisk.Value
' ws.Cells(NextRow, rg).Value = Me.cboRisk.Value
ws.Cells(NextRow, 3).Value = Me.txtProblem.Value
ws.Cells(NextRow, 6).Value = Me.cboStatus.Value
End Sub