Another Newby question about selecting and what it can do [longish]

[TBA]

This is a follow-up to the last post I made about selecting a range.

I feel it's necessary at this time to provide a little bit of background
about the table I am working with. I will try to be brief.

The company I work for does digital mapping for enhanced 911 systems. We
use GIS [Geographical Information Systems] software to manage and edit our
data. Each feature on a map (roads, structures) is known as a Shape file.
Each Shape file is in fact at least 3 separate files, all with the same file
name except for the file extension. One of the file extensions is *.dbf.
This file is the file that contains all the attribute data for the feature
on the map. It is a flat two-dimensional file and can be accessed from
within the GIS software. Sometimes it becomes necessary to modify this
*.dbf file in Excel to gain some added functionality.

So, from within my GIS software I have the option of exporting the *.dbf
file as a new *.dbf file. This file can be opened in Excel, edited, saved,
and then brought back in to the GIS software environment (there's a little
more to it than that, but you get the basic idea).

What I am doing is playing with a copy of one of our *.dbf files as sample
data to help me learn VBA. What I have done is exported the file as
described above, but when I open it with Excel I save it as an *.xls file to
avoid some minor hassles. This file, or table as our software likes to call
it, usually contains about 35-45 columns and anywhere from 100 to 10,000+
rows of data. The first row of the table is ALWAYS a header row. The
headers are text and basically describe the nature of the values in that
column.

(If you're still reading this, thank you!)

Yesterday I added some command buttons to the worksheet itself and coded one
of those buttons like this:

Private Sub CommandButton4_Click()

ActiveCell.CurrentRegion.Select

Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("A5").Select

End Sub

The code for doing the sort I got by recording a macro and then copying and
pasting the macro code into the code procedure for the command button. It
works just fine as long as you highlight a cell containing data from the
table before clicking on the command button. I know there is a way to
"trap" errors, and that I can make a message box come up to ask the user to
"Please select a value from the table containing data and try again." For
now I am assuming this isn't necessary.

So...I want to start out simple.

Q1: What is the code for the error message box? I'm assuming it's a type
of "OnError do this" statement.

Q2: I'd like to be able to dump all the column headers into a list, with
the hope that I can use the actual column header names as variables in the
coding.

And that's just for starters.

Lastly, a major caveat of what I'm trying to do is that the table can reside
ANYWHERE on the spreadsheet. The table can be of any size. It will ALWAYS
have column headers. Because of this I can't assume that the column header
for the first column will always be in cell A1. I am hoping that from the
selection statement "ActiveCell.CurrentRegion.Select" that I will be able to
extract some of the parameters of the table, like what the column headers
are, where they are, what the actual cell references are that define the
table extents, etc. These are all values that could easily be different
from one table to the next.

Any help with Q1 and Q2 is greatly appreciated. I hope this is clear as
mud.

Thanks for reading!!

-gk-

 

[Don Guillett]

suggest making your sort range a defined name, something like.

sortrng
=offset($a$2,0,0,counta($A:$A),6)

This is a follow-up to the last post I made about selecting a range.

I feel it's necessary at this time to provide a little bit of background
about the table I am working with. I will try to be brief.

The company I work for does digital mapping for enhanced 911 systems. We
use GIS [Geographical Information Systems] software to manage and edit our
data. Each feature on a map (roads, structures) is known as a Shape file.
Each Shape file is in fact at least 3 separate files, all with the same file
name except for the file extension. One of the file extensions is *.dbf.
This file is the file that contains all the attribute data for the feature
on the map. It is a flat two-dimensional file and can be accessed from
within the GIS software. Sometimes it becomes necessary to modify this
*.dbf file in Excel to gain some added functionality.

So, from within my GIS software I have the option of exporting the *.dbf
file as a new *.dbf file. This file can be opened in Excel, edited, saved,
and then brought back in to the GIS software environment (there's a little
more to it than that, but you get the basic idea).

What I am doing is playing with a copy of one of our *.dbf files as sample
data to help me learn VBA. What I have done is exported the file as
described above, but when I open it with Excel I save it as an *.xls file to
avoid some minor hassles. This file, or table as our software likes to call
it, usually contains about 35-45 columns and anywhere from 100 to 10,000+
rows of data. The first row of the table is ALWAYS a header row. The
headers are text and basically describe the nature of the values in that
column.

(If you're still reading this, thank you!)

Yesterday I added some command buttons to the worksheet itself and coded one
of those buttons like this:

Private Sub CommandButton4_Click()

ActiveCell.CurrentRegion.Select

Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("A5").Select

End Sub

The code for doing the sort I got by recording a macro and then copying and
pasting the macro code into the code procedure for the command button. It
works just fine as long as you highlight a cell containing data from the
table before clicking on the command button. I know there is a way to
"trap" errors, and that I can make a message box come up to ask the user to
"Please select a value from the table containing data and try again." For
now I am assuming this isn't necessary.

So...I want to start out simple.

Q1: What is the code for the error message box? I'm assuming it's a type
of "OnError do this" statement.

Q2: I'd like to be able to dump all the column headers into a list, with
the hope that I can use the actual column header names as variables in the
coding.

And that's just for starters.

Lastly, a major caveat of what I'm trying to do is that the table can reside
ANYWHERE on the spreadsheet. The table can be of any size. It will ALWAYS
have column headers. Because of this I can't assume that the column header
for the first column will always be in cell A1. I am hoping that from the
selection statement "ActiveCell.CurrentRegion.Select" that I will be able to
extract some of the parameters of the table, like what the column headers
are, where they are, what the actual cell references are that define the
table extents, etc. These are all values that could easily be different
from one table to the next.

Any help with Q1 and Q2 is greatly appreciated. I hope this is clear as
mud.

Thanks for reading!!

-gk-

 

[Tom Ogilvy]

When you open a dbf file in excel, it will always start in Cell A1 and the
data in the table will be specified in a defined name (insert=>Name=>Define)
named database. You can examine this defnition with

msgbox Range("Database").Address(external:=True)

you can get the upper left corner with

msgbox range("Database")(1).Address

you can step through the header with

i = 0
for each cell in Range("Database").Rows(1).Cells
i = i + 1
if i < 6 then ' no use looking at all 45
msgbox "header" & i & ": " & cell.Value
end if
Next

If you add data (New rows), then database will not expand to include that
data and if you save the file as dbf, the new data will not be saved unless
you redefine database - which you can do with

range("Database").CurrentRegion.Name = "Database"

If you want to know what area on the worksheet is considered Used

msgbox Activesheet.Usedrange.Address(external:=true)


Private Sub CommandButton4_Click()
dim rng as Range
dim varr as variant
dim i as long
dim sStr as string
set rng = ActiveCell.CurrentRegion
if rng.Address = ActiveCell.Address then
msgbox "Please select within the data table"
Exit Sub
End if
' or skip the above and use
' set rng = Range("Database").CurrentRegion
rng.Sort Key1:=rng(1).offset(0,2), Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

Range("A5").Select

varr = rng.rows(1)
for i = lbound(varr,2) to ubound(varr,2)
sStr = sStr & varr(1,i) & ", "
if i mod 5 = 0 then
sStr = sStr & vbNewLine
end if
Next
sStr = Left(sStr,len(sStr)-1)
msgbox sStr


End Sub


--
Regards,
Tom Ogilvy

This is a follow-up to the last post I made about selecting a range.

I feel it's necessary at this time to provide a little bit of background
about the table I am working with. I will try to be brief.

The company I work for does digital mapping for enhanced 911 systems. We
use GIS [Geographical Information Systems] software to manage and edit our
data. Each feature on a map (roads, structures) is known as a Shape file.
Each Shape file is in fact at least 3 separate files, all with the same file
name except for the file extension. One of the file extensions is *.dbf.
This file is the file that contains all the attribute data for the feature
on the map. It is a flat two-dimensional file and can be accessed from
within the GIS software. Sometimes it becomes necessary to modify this
*.dbf file in Excel to gain some added functionality.

So, from within my GIS software I have the option of exporting the *.dbf
file as a new *.dbf file. This file can be opened in Excel, edited, saved,
and then brought back in to the GIS software environment (there's a little
more to it than that, but you get the basic idea).

What I am doing is playing with a copy of one of our *.dbf files as sample
data to help me learn VBA. What I have done is exported the file as
described above, but when I open it with Excel I save it as an *.xls file to
avoid some minor hassles. This file, or table as our software likes to call
it, usually contains about 35-45 columns and anywhere from 100 to 10,000+
rows of data. The first row of the table is ALWAYS a header row. The
headers are text and basically describe the nature of the values in that
column.

(If you're still reading this, thank you!)

Yesterday I added some command buttons to the worksheet itself and coded one
of those buttons like this:

Private Sub CommandButton4_Click()

ActiveCell.CurrentRegion.Select

Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("A5").Select

End Sub

The code for doing the sort I got by recording a macro and then copying and
pasting the macro code into the code procedure for the command button. It
works just fine as long as you highlight a cell containing data from the
table before clicking on the command button. I know there is a way to
"trap" errors, and that I can make a message box come up to ask the user to
"Please select a value from the table containing data and try again." For
now I am assuming this isn't necessary.

So...I want to start out simple.

Q1: What is the code for the error message box? I'm assuming it's a type
of "OnError do this" statement.

Q2: I'd like to be able to dump all the column headers into a list, with
the hope that I can use the actual column header names as variables in the
coding.

And that's just for starters.

Lastly, a major caveat of what I'm trying to do is that the table can reside
ANYWHERE on the spreadsheet. The table can be of any size. It will ALWAYS
have column headers. Because of this I can't assume that the column header
for the first column will always be in cell A1. I am hoping that from the
selection statement "ActiveCell.CurrentRegion.Select" that I will be able to
extract some of the parameters of the table, like what the column headers
are, where they are, what the actual cell references are that define the
table extents, etc. These are all values that could easily be different
from one table to the next.

Any help with Q1 and Q2 is greatly appreciated. I hope this is clear as
mud.

Thanks for reading!!

-gk-

 

[TBA]

Once again, thank you Tom!

-gk-

When you open a dbf file in excel, it will always start in Cell A1 and the
data in the table will be specified in a defined name (insert=>Name=>Define)
named database. You can examine this defnition with

msgbox Range("Database").Address(external:=True)

you can get the upper left corner with

msgbox range("Database")(1).Address

you can step through the header with

i = 0
for each cell in Range("Database").Rows(1).Cells
i = i + 1
if i < 6 then ' no use looking at all 45
msgbox "header" & i & ": " & cell.Value
end if
Next

If you add data (New rows), then database will not expand to include that
data and if you save the file as dbf, the new data will not be saved unless
you redefine database - which you can do with

range("Database").CurrentRegion.Name = "Database"

If you want to know what area on the worksheet is considered Used

msgbox Activesheet.Usedrange.Address(external:=true)


Private Sub CommandButton4_Click()
dim rng as Range
dim varr as variant
dim i as long
dim sStr as string
set rng = ActiveCell.CurrentRegion
if rng.Address = ActiveCell.Address then
msgbox "Please select within the data table"
Exit Sub
End if
' or skip the above and use
' set rng = Range("Database").CurrentRegion
rng.Sort Key1:=rng(1).offset(0,2), Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

Range("A5").Select

varr = rng.rows(1)
for i = lbound(varr,2) to ubound(varr,2)
sStr = sStr & varr(1,i) & ", "
if i mod 5 = 0 then
sStr = sStr & vbNewLine
end if
Next
sStr = Left(sStr,len(sStr)-1)
msgbox sStr


End Sub


--
Regards,
Tom Ogilvy

This is a follow-up to the last post I made about selecting a range.

I feel it's necessary at this time to provide a little bit of background
about the table I am working with. I will try to be brief.

The company I work for does digital mapping for enhanced 911 systems. We
use GIS [Geographical Information Systems] software to manage and edit our
data. Each feature on a map (roads, structures) is known as a Shape file.
Each Shape file is in fact at least 3 separate files, all with the same file
name except for the file extension. One of the file extensions is *.dbf.
This file is the file that contains all the attribute data for the feature
on the map. It is a flat two-dimensional file and can be accessed from
within the GIS software. Sometimes it becomes necessary to modify this
*.dbf file in Excel to gain some added functionality.

So, from within my GIS software I have the option of exporting the *.dbf
file as a new *.dbf file. This file can be opened in Excel, edited, saved,
and then brought back in to the GIS software environment (there's a little
more to it than that, but you get the basic idea).

What I am doing is playing with a copy of one of our *.dbf files as sample
data to help me learn VBA. What I have done is exported the file as
described above, but when I open it with Excel I save it as an *.xls

file

to
avoid some minor hassles. This file, or table as our software likes to call
it, usually contains about 35-45 columns and anywhere from 100 to 10,000+
rows of data. The first row of the table is ALWAYS a header row. The
headers are text and basically describe the nature of the values in that
column.

(If you're still reading this, thank you!)

Yesterday I added some command buttons to the worksheet itself and coded one
of those buttons like this:

Private Sub CommandButton4_Click()

ActiveCell.CurrentRegion.Select

Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("A5").Select

End Sub

The code for doing the sort I got by recording a macro and then copying and
pasting the macro code into the code procedure for the command button. It
works just fine as long as you highlight a cell containing data from the
table before clicking on the command button. I know there is a way to
"trap" errors, and that I can make a message box come up to ask the user to
"Please select a value from the table containing data and try again." For
now I am assuming this isn't necessary.

So...I want to start out simple.

Q1: What is the code for the error message box? I'm assuming it's a type
of "OnError do this" statement.

Q2: I'd like to be able to dump all the column headers into a list, with
the hope that I can use the actual column header names as variables in the
coding.

And that's just for starters.

Lastly, a major caveat of what I'm trying to do is that the table can reside
ANYWHERE on the spreadsheet. The table can be of any size. It will ALWAYS
have column headers. Because of this I can't assume that the column header
for the first column will always be in cell A1. I am hoping that from the
selection statement "ActiveCell.CurrentRegion.Select" that I will be

able

to
extract some of the parameters of the table, like what the column headers
are, where they are, what the actual cell references are that define the
table extents, etc. These are all values that could easily be different
from one table to the next.

Any help with Q1 and Q2 is greatly appreciated. I hope this is clear as
mud.

Thanks for reading!!

-gk-