Hi,

I am trying to use the ShowDataForm method to provide a quick and easy
update approach for a series of reference data items in a workbook that has
been developed.

When I use the ShowDataForm method via the Data menu option within the
workbook is works fine and allows me to insert, edit and remove rows from the
data.

When I included as code within the workbook for some reason it only
recognises the first row of data in the worksheet and does not go to the
selected range.....is there anyway to ensure that ShowDataForm recognises the
selected range ? refer to the code below

Private Sub ReferenceUpdateButton_Click()

Sheets("Reference").Select
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ShowDataForm

End Sub

The Reference worksheet looks something like this


Reference Data
This worksheet contains all reference data including Building details for
the inspections and Price lists for the quotations

Unit Type Reference List
Unit Type Area Item Item Type Specifications Oaks Price Customer Price
Quantity
1 Bedroom Bathroom 1a Bath Building Works $- $-
1 Bedroom Bathroom 1 Doors/Walls Building Works $- $-
1 Bedroom Bathroom 1 Floor/floor coverings Building Works $- $-

Here's an article that explains how the problem occurs in VBA (with solution)
http://support.microsoft.com/default...;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

scott56hannah wrote:

Hi,

I am trying to use the ShowDataForm method to provide a quick and easy
update approach for a series of reference data items in a workbook that has
been developed.

When I use the ShowDataForm method via the Data menu option within the
workbook is works fine and allows me to insert, edit and remove rows from the
data.

When I included as code within the workbook for some reason it only
recognises the first row of data in the worksheet and does not go to the
selected range.....is there anyway to ensure that ShowDataForm recognises the
selected range ? refer to the code below

Private Sub ReferenceUpdateButton_Click()

Sheets("Reference").Select
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ShowDataForm

End Sub

The Reference worksheet looks something like this

Reference Data
This worksheet contains all reference data including Building details for
the inspections and Price lists for the quotations

Unit Type Reference List
Unit Type Area Item Item Type Specifications Oaks Price Customer Price
Quantity
1 Bedroom Bathroom 1a Bath Building Works $- $-
1 Bedroom Bathroom 1 Doors/Walls Building Works $- $-
1 Bedroom Bathroom 1 Floor/floor coverings Building Works $- $-

--

Dave Peterson

Thankyou....the article was very useful and has resolved my problem

"Dave Peterson" wrote:

Here's an article that explains how the problem occurs in VBA (with solution)
http://support.microsoft.com/default...;en-us;q110462
XL: ShowDataForm Method Fails If Data Can't Be Found

scott56hannah wrote:

Hi,

I am trying to use the ShowDataForm method to provide a quick and easy
update approach for a series of reference data items in a workbook that has
been developed.

When I use the ShowDataForm method via the Data menu option within the
workbook is works fine and allows me to insert, edit and remove rows from the
data.

When I included as code within the workbook for some reason it only
recognises the first row of data in the worksheet and does not go to the
selected range.....is there anyway to ensure that ShowDataForm recognises the
selected range ? refer to the code below

Private Sub ReferenceUpdateButton_Click()

Sheets("Reference").Select
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ShowDataForm

End Sub

The Reference worksheet looks something like this

Reference Data
This worksheet contains all reference data including Building details for
the inspections and Price lists for the quotations

Unit Type Reference List
Unit Type Area Item Item Type Specifications Oaks Price Customer Price
Quantity
1 Bedroom Bathroom 1a Bath Building Works $- $-
1 Bedroom Bathroom 1 Doors/Walls Building Works $- $-
1 Bedroom Bathroom 1 Floor/floor coverings Building Works $- $-

--

Dave Peterson