data form help

[dfmor]

I have an order form that I want users to be able to easily enter line items. The data, form, command would be perfect for this, BUT

My headings for this part of the form are in B14 rather than in A1 (the default list row). The headings appear something like this ("/" denotes column break):

STOCK NO / DESCRIPTION / UOI / QUANTITY

I can block the headings (B14:F14) and then manually invoke the data, form, command and click OK AND the headings appear correctly in the pop-up box. The data entered appears correctly in the first row below the headings and lets me keep adding line items.

Problem is, if I try to create a macro to calls this up, it always calls up the first row (A1) headings in the spreadsheet for the input names/headings in the pop-up box. I want to attach a macro to a button for ease of the user.

The macro code is rather basic - here it is:

Range("B14:F14").Select
ActiveSheet.ShowDataForm

Anyone know a workaround - if possible? That is, what code can I add to the macro to make it select the range column headings(B14:F14) rather than the default top row (A1)?

Your thoughts/help greatly appreciated.

dfmor

 

[OdinsDream]

Could you post the userform code you have in your project? I'm not really understanding the link established between Cell A1 and the form.

Also, you can use:
<span style="font-family:verdana">
UserFormName.Show</span>

instead of ActiveSheet

 

[dfmor]

Sorry, I'm not making this clear.

As far as I know, the "ShowDataForm" is the code. I didn't create a UserForm in the editor. I'm using an existing commmand from the excel drop-down menu.

Try this: Open a blank sheet, type in column titles in the first row (A1-D1). Now from excel commands at the top, click on "Data", then "Form", and a pop-up box appears. You enter data in the input boxes and they will appear in A2:D2. You can then click on "New" to add the next row of data (A3:D3), and so forth.

Problem: The column titles I want to use with the command "data", "form", are in row 14 of my spreadsheet rather than in row 1. Trying to run a macro to invoke the "data", "form" command always pulls up row 1's column titles.

May not be a good (simply) workaround for this.

Again, sorry if I wasn't clear on this. I'm not an advanced macro "guru" - I know enough to write the basic/intermediate macros to get by.

Thanks for your help,

dfmor

 

[Mudface]

This is a bit of an odd one and unfortunately, I don't see there being a workaround, other than you moving your data around so it becomes the 'default' location for the data form, I'm afraid.