ShowDataForm of Worksheet class failed

[Don Cossitt]

Sub AddProduct()
Application.ScreenUpdating = False

Range("AA1:AB1").Select
Range(Selection, Selection.End(xlDown)).Select ' error with or
without this line
Application.CutCopyMode = False ' error
with or without this line
ActiveSheet.ShowDataForm '
produces the error

Application.ScreenUpdating = True

End Sub

I can produce the form manually selecting Data|Form. However, when trying
to add more items, get "cannot extend list or database".

Any ideas?

TIA
doco

 

[Leith Ross]

Hello Don,

Try this method....

With ActiveSheet
.Range("AA1:AB1").Select
.ShowDataForm
End With

ActiveCell.Select

Sincerely,
Leith Ros

 

[Mike Fogleman]

Try naming the range "Database" like this:

Sub AddProduct()
Application.ScreenUpdating = False

Range("AA1:AB1").Select
Range(Selection, Selection.End(xlDown)).Select ' error with or
without this line

Selection.Name = "Database"

Application.CutCopyMode = False ' error
with or without this line
ActiveSheet.ShowDataForm '
produces the error

Application.ScreenUpdating = True

End Sub

Mike F

 

[doco]

That did the trick. I am not sure why that would matter but - it worked
thanks.

doco

 

[Mike Fogleman]

Excel tries to determine on its own where the data table is by contiguous
cells, when you call for the DataForm. If you have more than one area of
contiguous cells, then Excel is confused as to which table it should use,
and therefore errors. You tell Excel which table to use by naming it
"Database". I have a workbook with 12 little 'Databases' on one hidden
sheet. Each area is separated by blank cells. When I want the DataForm to
show a certain area, I activate the upper left cell of the area and tell
Excel Activecell.CurrentRegion.Name = "Database". Then the DataForm knows to
use that area only.
I hope this explains it a little clearer.

Mike F

 

[doco]

Interestingly, it will only work if the first list is in column A. Placed
in any other column produces and error. Is this a bug or is there a
workaround?

TIA
doco

 

[Mike Fogleman]

I can't imagine why not. I tried inserting a blank column A ahead of my
data, once I changed the reference, it still worked as before.

Mike F

 

[doco]

A1:B1 contain headers for a list 90 rows deep. D1:F1 contain headers for
list only two deep. When accessing list two @ D1:F1programmatically, data
form shows list A1:B1, not D1:F1. If I move 'table' two 19 cols to the
right and access programmatically; I get the original worksheet class
failure error, but works fine if I manually [Data|Form] select the list.

The dataform will only work programatically if the list begins in A1 of any
sheet.

Its maddening!
doco

 

[Mike Fogleman]

I sent you a working to sample to play with.

Mike F

A1:B1 contain headers for a list 90 rows deep. D1:F1 contain headers for
list only two deep. When accessing list two @ D1:F1programmatically, data
form shows list A1:B1, not D1:F1. If I move 'table' two 19 cols to the
right and access programmatically; I get the original worksheet class
failure error, but works fine if I manually [Data|Form] select the list.

The dataform will only work programatically if the list begins in A1 of
any sheet.

Its maddening!
doco

I can't imagine why not. I tried inserting a blank column A ahead of my
data, once I changed the reference, it still worked as before.

Mike F

 

[doco]

Got it! Thanks
doco

I sent you a working to sample to play with.

Mike F

A1:B1 contain headers for a list 90 rows deep. D1:F1 contain headers for
list only two deep. When accessing list two @ D1:F1programmatically,
data form shows list A1:B1, not D1:F1. If I move 'table' two 19 cols to
the right and access programmatically; I get the original worksheet class
failure error, but works fine if I manually [Data|Form] select the list.

The dataform will only work programatically if the list begins in A1 of
any sheet.

Its maddening!
doco

I can't imagine why not. I tried inserting a blank column A ahead of my
data, once I changed the reference, it still worked as before.

Mike F
Interestingly, it will only work if the first list is in column A.
Placed in any other column produces and error. Is this a bug or is
there a workaround?

TIA
doco



That did the trick. I am not sure why that would matter but - it
worked thanks.

doco


Try naming the range "Database" like this:

Sub AddProduct()
Application.ScreenUpdating = False

Range("AA1:AB1").Select
Range(Selection, Selection.End(xlDown)).Select ' error
with or
without this line

Selection.Name = "Database"

Application.CutCopyMode = False ' error
with or without this line
ActiveSheet.ShowDataForm '
produces the error

Application.ScreenUpdating = True

End Sub

Mike F

Sub AddProduct()
Application.ScreenUpdating = False

Range("AA1:AB1").Select
Range(Selection, Selection.End(xlDown)).Select ' error
with or without this line
Application.CutCopyMode = False '
error with or without this line
ActiveSheet.ShowDataForm '
produces the error

Application.ScreenUpdating = True

End Sub

I can produce the form manually selecting Data|Form. However, when
trying to add more items, get "cannot extend list or database".

Any ideas?

TIA
doco