Open a form via macro 1

[eti3nne]

I have an excel spreadsheet which contains 3 lists on the same page, side by side. I have selected any one of the lists and clicked on Data ... Form .. and I am able to add or delete entries in the correctly selected list.

I want to be able to just click on one of 3 buttons (one per list) and have the selected list pop up in the form. I have recorded a macro when doing this manually, but when I put this into the code behind my button, the form always picks up the left most list. How do I force the form to link to the correct list?

Any ideas?

Thanks for looking.

 

[soccercook44]

Please post your code. So someone can help you out.

 

[combo]

You need to select (by code) any cell in the list and next programmatically execute the dataform menu item:

Application.CommandBars.FindControl(Type:=msoControlButton, ID:=860, Visible:=False).Execute

Additional profit: this method keeps local formats.

combo

 

[eti3nne]

Here is the output from the macro recorder ( plus my comments):-

Sheets("Lookups").Select 'This sheet has the 3 lists on it, in columns B, D and F.
Range("F3").Select 'This is where the third list starts
ActiveSheet.ShowDataForm 'This causes the form to open and allows me to add a new item

Any help appreciated.

Thanks for looking.

 

[combo]

Have you tried the FindControl trick? In your case (ID=860 Data>Form menu item):

Sheets("Lookups").Select 'This sheet has the 3 lists on it, in columns B, D and F.
Range("F3").Select 'This is where the third list starts
Application.CommandBars.FindControl(Type:=msoControlButton, ID:=860, Visible:=False).Execute ' simulates menu action

combo

 

[eti3nne]

That's handy. Cheers, combo. Your help much appreciated. *

BTW: Where/how does one discover these seemingly hidden control codes?

 

[PHV]

Where/how does one discover these seemingly hidden control codes?
Usually with the F2 and F1 keys when in the VBE.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

After adding break point at 'End Sub' in

Sub Test()
Dim xlapp As Application
Set xlapp = Application
End Sub

it is possible to examine application object in the 'Locals' window. Here: 'CommandBars', 'Item1' etc.

combo