#1
|
|||
|
|||
How to use named ranges in excel vba?
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 Last edited by bosve73; 01-25-2012 at 02:46 AM. Reason: code tag |
#2
|
|||
|
|||
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.
Code:
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 |
#3
|
||||
|
||||
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): Code:
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 |
#4
|
|||
|
|||
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.
Code:
lNextRow = wksDb.Cells(wksDb.Rows.Count, rngRisk.Column).End(xlUp).Offset(1, 0).Row How come i don't need to add the rest of the ranges(problem and status)? |
#5
|
||||
|
||||
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.
|
Thread Tools | |
Display Modes | |
|
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 |