help with dataform and sorting macros
 

I created a dataform macro in Excel with the Auto_Open
name. I need the dataform to be for just the headings in
columns A-G, although there are column headings in A-P, H-
P aren't needed for the average data entry person. Does
anyone have any suggestions?

I also need to have a column G sort ascending upon close,
Auto_Close macro. I got it started, but since Row 1 is a
merged cell, and isn't included in the macro, I keep
getting a debugging error. Need to sort G2--end of data.
Any suggestions?

I can provide copies of the macro wording that I tried to
use if you need to see it.

Thanks!
Beth

help with dataform and sorting macros
 

When working on the dataform macro, I get an error -
debugging message - when I run this at the line:
Set myRng = Worksheets("sheet1").Range("a:g")
Any suggestions?

Thanks,
Beth

-----Original Message-----
If you name your range, excel will look there to find
your database:

Option Explicit
Sub testme()

Dim myRng As Range
Set myRng = Worksheets("sheet1").Range("a:g")
myRng.Name = "database"
Worksheets("sheet1").ShowDataForm

End Sub

In your auto_close, maybe you could sort your data with
headers:=xlno. (just
start with the real data--row 2???).

with worksheets("sheet1")
.range("a2:g" & .cells(.rows.count,"A").end
(xlup).row).sort .....
end with

I used the last cell in column A to find the end of
data. And I only sorted
through G. Fix both of those to match your data.)



Beth Mc wrote:

I created a dataform macro in Excel with the Auto_Open
name. I need the dataform to be for just the headings in
columns A-G, although there are column headings in A-P,
H-
P aren't needed for the average data entry person. Does
anyone have any suggestions?

I also need to have a column G sort ascending upon
close,
Auto_Close macro. I got it started, but since Row 1 is a
merged cell, and isn't included in the macro, I keep
getting a debugging error. Need to sort G2--end of data.
Any suggestions?

I can provide copies of the macro wording that I tried
to
use if you need to see it.

Thanks!
Beth

--

Dave Peterson

.

help with dataform and sorting macros
 

Okay, I'm confused on the sorting macro wording that you
listed. What is headers-xlno? I'm fairly new to macros, so
spelling it out like you did for the dataform really helps
me.

My sort at auto_close is now sorting the data, but it is
moving the header row of A2:G2 below the data when it
sorts. So instead of A2:G2 showing the column headers, the
column headers are moved to A11:G11 and the data is sorted
(correctly) in A2:G10. The column headers for H2:P2 stay
in the same place.

Thanks for the help!
Beth

-----Original Message-----
If you name your range, excel will look there to find
your database:

Option Explicit
Sub testme()

Dim myRng As Range
Set myRng = Worksheets("sheet1").Range("a:g")
myRng.Name = "database"
Worksheets("sheet1").ShowDataForm

End Sub

In your auto_close, maybe you could sort your data with
headers:=xlno. (just
start with the real data--row 2???).

with worksheets("sheet1")
.range("a2:g" & .cells(.rows.count,"A").end
(xlup).row).sort .....
end with

I used the last cell in column A to find the end of
data. And I only sorted
through G. Fix both of those to match your data.)



Beth Mc wrote:

I created a dataform macro in Excel with the Auto_Open
name. I need the dataform to be for just the headings in
columns A-G, although there are column headings in A-P,
H-
P aren't needed for the average data entry person. Does
anyone have any suggestions?

I also need to have a column G sort ascending upon
close,
Auto_Close macro. I got it started, but since Row 1 is a
merged cell, and isn't included in the macro, I keep
getting a debugging error. Need to sort G2--end of data.
Any suggestions?

I can provide copies of the macro wording that I tried
to
use if you need to see it.

Thanks!
Beth

--

Dave Peterson

.

help with dataform and sorting macros
 

Actually, the "s" was a typo.

When you recorded a sort macro, what did the code look like?

For example (in xl2002), I got this when I recorded a macro:


Range("A2:W26").Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

The Header:= can be set to xlYes (it has headers), xlNo (no headers) and xlGuess
(let xl decide). If I know, then I won't let excel guess.

I used two sort fields, G and C. (If you don't need two, then don't specify
them when you're recording the macro.)

And I wasn't sure what columns you wanted sorted and what end of data meant.

But if I assumed that column G always had something in it (so I could use that
to determine the end), I could modify my recorded code to look more like:

dim myRng as range
dim LastRow as long
with worksheets("sheet1") '<--- fix this again!
lastrow = .cells(.rows.count,"G").end(xlup).row
set myrng = .range("a1:P" & lastrow) 'adjust P to be last column
'now comes the recorded part
myrng.sort Key1:=.Range("G2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
end with

Watch out for the sheet name and last column. And if you have a better column
that can define the lastused row, modify that "Lastrow = " line, too.

If this doesn't work, post what you used. And be careful with your sort. If
you don't include H:P in the range sorted, then those cells won't move. I'm not
sure if that portion needed to be sorted.



Beth Mc wrote:

Okay, I'm confused on the sorting macro wording that you
listed. What is headers-xlno? I'm fairly new to macros, so
spelling it out like you did for the dataform really helps
me.

My sort at auto_close is now sorting the data, but it is
moving the header row of A2:G2 below the data when it
sorts. So instead of A2:G2 showing the column headers, the
column headers are moved to A11:G11 and the data is sorted
(correctly) in A2:G10. The column headers for H2:P2 stay
in the same place.

Thanks for the help!
Beth

-----Original Message-----
If you name your range, excel will look there to find
your database:

Option Explicit
Sub testme()

Dim myRng As Range
Set myRng = Worksheets("sheet1").Range("a:g")
myRng.Name = "database"
Worksheets("sheet1").ShowDataForm

End Sub

In your auto_close, maybe you could sort your data with
headers:=xlno. (just
start with the real data--row 2???).

with worksheets("sheet1")
.range("a2:g" & .cells(.rows.count,"A").end
(xlup).row).sort .....
end with

I used the last cell in column A to find the end of
data. And I only sorted
through G. Fix both of those to match your data.)



Beth Mc wrote:

I created a dataform macro in Excel with the Auto_Open
name. I need the dataform to be for just the headings in
columns A-G, although there are column headings in A-P,
H-
P aren't needed for the average data entry person. Does
anyone have any suggestions?

I also need to have a column G sort ascending upon
close,
Auto_Close macro. I got it started, but since Row 1 is a
merged cell, and isn't included in the macro, I keep
getting a debugging error. Need to sort G2--end of data.
Any suggestions?

I can provide copies of the macro wording that I tried
to
use if you need to see it.

Thanks!
Beth

--

Dave Peterson

.


--

Dave Peterson