How to reposition the default data form with code?

Sorry Joe,

Could replicate EXACTLY your problem, but couldn't get it to work, in fact, i wasn't able to show the DataForm at all !! never !! this is very strange...

Juan Pablo G. Reset the position to calibrate view moves.

Wait a sec... seeing a pattern.

I created a new sheet with a simple database, put in a macro that has this

ActiveSheet.ShowDataForm

and it worked, no matter where the activecell was.

If i create under this one another database and again, run the macro, Excel still assumes the first one (Begining in A1). But if i go through "keystrokes", i can show the second database... i think Excel assumes the list starts always in A1 and if it doesn't find one, crashes...

Juan Pablo G.

Re: Wait a sec... seeing a pattern.

Juan,
Good to see you online.
I only want one default data form on the sheet.
As a test the lables are in AA1 through AE1, with the first data in AA2.
I select AA2 then scroll to A1, so the data table is off the screen.
Then do the menu to show the data form and it works. The data form comes up with my lables and populates the table off to the right of the screen.

The problem is when I do the same with a macro it fails? JSW
P.S. I sent you my E-Mail address.

Re: Wait a sec... seeing a pattern.

I know, i read your mail (Very nice !). What i saw (Let me confirm it right now) is this. I have a database going from A through R. I run the macro, it's allright (I have nothing else on the sheet). I inserted a column in A, so the data starts now in B (B:S), and it's still visible on the screen, whatever cell is selected and the macro still runs. But if i insert another column the macro fails...

However, managed to come up with a very, VERY, nasty solution... Sendkeys. I select a cell near the list, and run this:

SendKeys "%tl"

(This is Spanish version, i'm sure you'll have to change this, this is Alt T, L)

and it worked... so, maybe this can solve the problem for now.

Juan Pablo G.

I tried hiding A:B, scrolling, doing what i imagine you have tried, and nothing, Excel won't recognize it, but if i go manually, it works...

Re: Wait a sec... seeing a pattern.

Alt T pulls up the macro menu and never gets to L.

The problem is not doing it, its geting a macro to do it all. I tried just the show command and it worked if I did every other step manually but failed the next time I ran it. It seems that if a macro selects the data column then the showDataForm fails?

What does Alt T,L do on your system? So I can try and reproduce it here? JSW

Re: Wait a sec... seeing a pattern.

Alt T, L is the command to display the DataForm.

Alt, DaTos (Tools), FormuLario (DataForm)... the alt keys should be different in english, but i don't have Excel in english here...

Juan Pablo G. Alt T pulls up the macro menu and never gets to L. The problem is not doing it, its geting a macro to do it all. I tried just the show command and it worked if I did every other step manually but failed the next time I ran it. It seems that if a macro selects the data column then the showDataForm fails? What does Alt T,L do on your system? So I can try and reproduce it here? JSW

Re: Wait a sec... seeing a pattern.

Your Alt TL is MY ALT DO, D for Data and O for fOrm as F is taken by Filter.

I recorded this with ALT DO and got the same macro as when done manually, which works when recorded but not when the macro is run?

This is one which should be easy, but its a brain puzzle?

I would put the data table on another sheet and build a menu sheet, but the showForm only works on the current sheet. So, I tried to put the data table off to the right, so the lables of the data table are not visible to the user, when the form is called. For a clean look to the application. The problem is every macro fails to do this, but it can be done manually? JSW

Alt T, L is the command to display the DataForm. Alt, DaTos (Tools), FormuLario (DataForm)... the alt keys should be different in english, but i don't have Excel in english here... : Alt T pulls up the macro menu and never gets to L. : The problem is not doing it, its geting a macro to do it all. I tried just the show command and it worked if I did every other step manually but failed the next time I ran it. It seems that if a macro selects the data column then the showDataForm fails? : What does Alt T,L do on your system? So I can try and reproduce it here? JSW :

Re: Wait a sec... seeing a pattern.

Joe, instead of putting

ActiveSheet.ShowDataForm 'I think this is what the recorder gives you, put...

SendKeys "%do"

And it *should* work ok (I got it working here)

Juan Pablo G. Your Alt TL is MY ALT DO, D for Data and O for fOrm as F is taken by Filter. I recorded this with ALT DO and got the same macro as when done manually, which works when recorded but not when the macro is run? This is one which should be easy, but its a brain puzzle? I would put the data table on another sheet and build a menu sheet, but the showForm only works on the current sheet. So, I tried to put the data table off to the right, so the lables of the data table are not visible to the user, when the form is called. For a clean look to the application. The problem is every macro fails to do this, but it can be done manually? JSW

Your the Master, Calling through the keyboard in the macro worked!

Thanks Juan,
I thought some alternate way of calling the showForm was needed thats why I tried calling a macro from a macro. I never would have thought of using the sendKeys command thought, as I thought it was the same as using the menu. In testing it, it was the same, as you pointed out they both put ShowForm in the recorded macro.

Very clever of you to use the Send Keys inplace of the show command. Thanks this was driving me up the wall. JSW Joe, instead of putting 'I think this is what the recorder gives you, put... SendKeys "%do" And it *should* work ok (I got it working here) : Your Alt TL is MY ALT DO, D for Data and O for fOrm as F is taken by Filter. : I recorded this with ALT DO and got the same macro as when done manually, which works when recorded but not when the macro is run? : This is one which should be easy, but its a brain puzzle? : I would put the data table on another sheet and build a menu sheet, but the showForm only works on the current sheet. So, I tried to put the data table off to the right, so the lables of the data table are not visible to the user, when the form is called. For a clean look to the application. The problem is every macro fails to do this, but it can be done manually? JSW