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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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