How to use named ranges in excel vba?

[bosve73]

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.

 
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

[JBeaucaire]

Based on what I see, even that isn't needed. You can look up column A of the Databas sheet and find the last row with data, add 1, then insert values across that row as per your form.

Option Explicit

Public Sub Save_Click()
Dim NextRow As Long

With Sheets("Databas")
    NextRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & NextRow).Value = Me.cboRisk.Value
    .Range("C" & NextRow).Value = Me.txtProblem.Value
    .Range("F" & NextRow).Value = Me.cboStatus.Value
End With

End Sub

[Colin Legg]

I think bosve73 finds there is a problem if someone starts inserting columns in the sheet though. Ideally this wouldn't happen and some sort of structural protection would be enforced, but using named ranges is a good safeguard. You could actually have 3 named ranges, one for each of the "Risk", "Problem" and "Status" columns.

Then your code would be something like this (I've broken it out into logical steps):

Public Sub Save_Click()
    Dim lNextRow As Long
    Dim wksDb As Worksheet
    
    Dim rngRisk As Range
    Dim rngProblem As Range
    Dim rngStatus As Range
    
    Set wksDb = Worksheets("Databas")
    
    Set rngRisk = wksDb.Range("Risk")
    Set rngProblem = wksDb.Range("Problem")
    Set rngStatus = wksDb.Range("Status")
    
    ' The statement simulates activating the last cell in the "risk" column, 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 the "risk" column  — even if the data
    ' area doesn’t begin in row 1 and contains blank rows.
    lNextRow = wksDb.Cells(wksDb.Rows.Count, rngRisk.Column).End(xlUp).Offset(1, 0).Row
    
    wksDb.Cells(lNextRow, rngRisk.Column) = Me.cboRisk.Value
    wksDb.Cells(lNextRow, rngProblem.Column) = Me.txtProblem.Value
    wksDb.Cells(lNextRow, rngStatus.Column) = Me.cboStatus.Value
End Sub

[bosve73]

Thank you Collin that was exactly what I was looking for. I tried your code and it works perfect. I still have some questions since I really want to learn more about VBA.

lNextRow = wksDb.Cells(wksDb.Rows.Count, rngRisk.Column).End(xlUp).Offset(1, 0).Row

I understand how the rest of the code works except the line above, to be more specific (wksDb.Rows.Count, rngRisk.Column).End

How come i don't need to add the rest of the ranges(problem and status)?

[Colin Legg]

That's a very good question and the answer is that the code makes a big assumption (this assumption was already included in your original post). When the VBA code determines the first free row, it does so by only checking the Risk column. It assumes that the first free row in the risk column will be the same for the others. If that may not be the case then you should change the code to check them all.