Add field based on excel data with keyboard shortcut

Guimenez · #16 01-05-2017, 05:36 AM

Quote:

Originally Posted by macropod

I have no idea what you mean. For example, pressing F8 has nothing to do with making a dropdown selection. If you want to make a dropdown selection, click on the dropdown. Nothing could be simpler and you certainly don't need a shortcut - unless you're trying to avoid using the mouse - but even then a shortcut won't make the selection for you.

I will try to explain me better:

Everyday i write documents talking about people and every time i talk about someone i need to put his name and number. So i'm trying to avoid writing the name and searching his number.

The purpose of using a shortcut it's to simplify the work: everytime i need to mention someone i will press F8 adding the Dropdown and choosing the correct name.

Ex:

The joao n.º123456 it working good for now. the paulo n.º324543 is missing the sessions. the jorge n.º235631 is wroking harder to complish the session.

Hope i've clarified you

macropod · #17 01-05-2017, 02:12 PM

But that is not the function of the F8 key, which is to extend a selection. See: https://support.office.com/en-us/art...-f762f663ceb2?

I should also point out that, as coded, the dropdown will only let you choose one entry per document and that, every time you open the document, the dropdown list will be reset. To prevent that, you would need to save the document as a template and make some coding changes. The advantage is that the macro now only needs to exist in the template - the documents it creates can all be saved in the docx format. See attached.

Guimenez · #18 01-06-2017, 01:31 AM

Quote:

Originally Posted by macropod

But that is not the function of the F8 key, which is to extend a selection. See: https://support.office.com/en-us/art...-f762f663ceb2?

I should also point out that, as coded, the dropdown will only let you choose one entry per document and that, every time you open the document, the dropdown list will be reset. To prevent that, you would need to save the document as a template and make some coding changes. The advantage is that the macro now only needs to exist in the template - the documents it creates can all be saved in the docx format. See attached.

Thanks

What i mean is, can i record a macro to use any keyboard shortcut to add the dropdown to choose the name? I mean, i will need lots of dropdown since i write minimium of 10 persons by document.

Maybe it's not possible

macropod · #19 01-06-2017, 03:23 AM

Sorry, but that just isn't how these things work. What you'd need for the scenario you envisage is a macro that not only calls the data from Excel but also creates both the dropdown it's going to populate and the one the data gets output to. Sure, it's possible, but that's a lot of coding effort just to spare you writing a few details. If these are names & details you re-use frequently in the same document or otherwise on a regular basis, you'd do better to create a custom quick part for each one and recall each as & when you need them. See: https://support.office.com/en-gb/art...-41c771847a7b?

Guimenez · #20 01-06-2017, 05:07 AM

Quote:

Originally Posted by macropod

Sorry, but that just isn't how these things work. What you'd need for the scenario you envisage is a macro that not only calls the data from Excel but also creates both the dropdown it's going to populate and the one the data gets output to. Sure, it's possible, but that's a lot of coding effort just to spare you writing a few details. If these are names & details you re-use frequently in the same document or otherwise on a regular basis, you'd do better to create a custom quick part for each one and recall each as & when you need them. See: https://support.office.com/en-gb/art...-41c771847a7b?

Thank you
I will try it out

Guimenez · #21 01-09-2017, 05:02 AM

If i record a macro with a keyboard shortcut and that macro will create a new dropdown with "ID". It works, but only show records if i save, close the document and then open the document.
Is there anyway to refresh the dropdown data after the same have been created?
If so, it will solve my problem

Thanks

macropod · #22 01-09-2017, 02:38 PM

At the end of your recorded macro, add the line:
Call Document_Open

Guimenez · #23 01-10-2017, 04:28 AM

Quote:

Originally Posted by macropod

At the end of your recorded macro, add the line:
Call Document_Open

When i put that code in my recorded macro, it stop with an error.

I've attached my macro.

Thank you

macropod · #24 01-10-2017, 02:21 PM

Try the attached template. When you create a new document from it, two content controls are created and the first, a dropdown, is populated from the Excel workbook. To add more content control pairs (after making a selection from the first content control, thereby deleting the first pair), all you need do is run the AddControls macro.

Guimenez · #25 01-11-2017, 01:19 AM

Quote:

Originally Posted by macropod

Try the attached template. When you create a new document from it, two content controls are created and the first, a dropdown, is populated from the Excel workbook. To add more content control pairs (after making a selection from the first content control, thereby deleting the first pair), all you need do is run the AddControls macro.

Hi,
the first time i open the document i can choose the a name and it will put the number(perfect), but after pressing F8(the shortcut i made for insert the new dropdown), it insert the dropdown but without names and then gives me the error attached

One again thank you for trying to help me

macropod · #26 01-11-2017, 01:22 AM

Without being able to see both the document behind the message box or the code line generating the error, I cannot advise further - the code works fine for me.

Guimenez · #27 01-11-2017, 01:26 AM

Quote:

Originally Posted by macropod

Without being able to see both the document behind the message box or the code line generating the error, I cannot advise further - the code works fine for me.

Sorry...
Here you have it

macropod · #28 01-11-2017, 01:35 AM

It appears you deleted the second new content control created by the 'AddControls' sub before selecting the dropdown.

Guimenez · #29 01-11-2017, 01:39 AM

Thanks,
When i press F8 my macro creates only the dropdown, how can i make to create the second control?

Guimenez · #30 01-11-2017, 01:44 AM

Your macro works amazing well

But how can i define F8 to run your macro?

EDIT: I done it.

I've record a macro to run your macro.

Thank you man, it's amazing perfect

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Changing keyboard layout - Changing/removing keyboard shortcut	Hans L	Windows	2	08-16-2015 05:33 PM
keyboard shortcut	ilor	OneNote	1	11-13-2014 02:48 PM
disable ctrl+space excel keyboard shortcut	melvinjn	Excel	2	11-27-2013 04:11 AM
Keyboard shortcut for bookmarks	Jennifer Murphy	Word VBA	1	01-06-2013 07:38 PM
Outlook Keyboard shortcut	sostrow28	Outlook	1	12-10-2010 05:57 AM