Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-25-2012, 01:15 AM
bosve73 bosve73 is offline Windows XP Office 2003
Novice
 
Join Date: Aug 2010
Posts: 6
bosve73 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 01-25-2012, 02:03 AM
JBeaucaire JBeaucaire is offline Windows XP Office 2003
Advanced Beginner
 
Join Date: Dec 2011
Posts: 51
JBeaucaire is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 01-25-2012, 02:46 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

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
Reply With Quote
  #4  
Old 01-25-2012, 06:25 AM
bosve73 bosve73 is offline Windows XP Office 2003
Novice
 
Join Date: Aug 2010
Posts: 6
bosve73 is on a distinguished road
Default

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
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)?
Reply With Quote
  #5  
Old 01-25-2012, 09:26 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
Reply

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


All times are GMT -7. The time now is 05:12 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft