Range name Database only works on one sheet

[Brian]

All,

The problem I have is that I have 4 sheets containing data. I want to call
the showdataform from a command buttons based on the sheets. I have named the
range "database" in the first sheet, but if I try to apply it to the second
sheet, then all command buttons pick up the last assigned "database" name. In
other words, is there a way to have the database name in all sheet ranges?

Private Sub CommandButton1_Click()

Range("A3:H33").Name = "Database"
SendKeys "%w", False
ActiveSheet.ShowDataForm
End Sub

Private Sub CommandButton2_Click()
Range("A3:H20").Name = "Database"
SendKeys "%w", False
ActiveSheet.ShowDataForm
End Sub

Thanks,

 

[Brian]

Worked it out.

Added code for each Command Button to Delete the Name, then re-create with
the new range.

Sheets("Guidance").Select
ActiveWorkbook.Names("Database").Delete
Range("A3:H20").Select
ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=Guidance!R3C1:R20C8"

May be messy, but it worked. Starting to enjoy Excel VBA.

 

[Dave Peterson]

Instead of using a workbook level range name, maybe using a sheet level name
would be better:

Range("A3:H33").Name = "Database"
becomes
me.Range("A3:H33").Name = "'" & me.name & "'!" & "Database"

Assuming that each of those buttons are on separate sheets.

or

with worksheets("somesheetnamehere")
.Range("A3:H33").Name = "'" & .name & "'!" & "Database"
end with

Since you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

 

[Brian]

Dave,

Thanks for the reply. I'll check that out.

May I ask one more question? In the code, I am tryingh to make the list
expand. At the moment, it will only allow me to add one new record. I have
search the forums, and google, but for the life of nme, I can't seem to get
anything to work. How do I amend the range to allow for the expansion?

Unfortunatly, this week is when my wife is on her annual trip to see her
mother; She is an excel VBA guru.

Private Sub CommandButton1_Click()
Sheets("GSOPs").Select
ActiveWorkbook.Names("Database").Delete
Range("A3:H32").Select
ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=GSOPs!R3C1:R25C8"
Range("A3:H32").Name = "Database"
SendKeys "%w", False
ActiveSheet.ShowDataForm
End Sub

Thanks,

 

[Dave Peterson]

I'd do something like:

dim LastRow as long
with me 'the sheet with the button
'can column A be used to determine if the row should be included?
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("A3:H" & lastrow).name = "'" & .name & "'!Database"
end with
SendKeys "%w", False
me.ShowDataForm

=====
You may want to look at defining a worksheet level name (local name) that
expands and contracts when data is added/deleted.

Debra Dalgleish explains it here:
http://contextures.com/xlNames01.html#Dynamic

Make sure you make it a local name: GSOPs!database

Dave,

Thanks for the reply. I'll check that out.

May I ask one more question? In the code, I am tryingh to make the list
expand. At the moment, it will only allow me to add one new record. I have
search the forums, and google, but for the life of nme, I can't seem to get
anything to work. How do I amend the range to allow for the expansion?

Unfortunatly, this week is when my wife is on her annual trip to see her
mother; She is an excel VBA guru.

Private Sub CommandButton1_Click()
Sheets("GSOPs").Select
ActiveWorkbook.Names("Database").Delete
Range("A3:H32").Select
ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=GSOPs!R3C1:R25C8"
Range("A3:H32").Name = "Database"
SendKeys "%w", False
ActiveSheet.ShowDataForm
End Sub

Thanks,

 

[Brian]

Thanks for all your help Dave.

Used Debra's instructions. All working (nearly).

Have a few minor things to fix. If I get stuck you will probably see another
post, but I have a few ideas I want to try first.

Thanks again