How to use named ranges in excel vba?

bosve73 · #1 01-25-2012, 01:15 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
email as pdf attachment - subject line and attachment named after mail merge	Nexus	Mail Merge	12	04-13-2011 11:34 PM
Named range drop-down	jgelpi16	Excel	1	04-08-2011 03:08 PM
Dynamic Named Ranges using text	hannu	Excel	0	06-22-2010 04:42 PM
Can't import home adresses in outlook 2010 from excel named ranges	eekie	Outlook	0	05-14-2010 02:04 PM
Opening multiple Excel files within the same Excel window.	lost9471	Excel	2	05-01-2010 01:57 PM