Hi All,
I am new to the forum and macros/VB. I am working on a personal project and have looked around this forum and the web to find tips and solutions to my issue below but unfortunately I couldn't manage to get a full code together due to my minimal knowledge in Coding.
I am using Excel 2013, I have a template sheet which is linked to a database (separate sheet) containing codes that represent data and related information that fall under 6 fields. On the template sheet you need to enter the code in column D according to the item description which is in Column I. I have successfully managed to link the template sheet to my database to get and extract all the required fields using Vlookup function.
In the template sheet i need to do the data entry for three cells for every item description (Code (Column D), Notes 1 (Column AA) and Query (Column AB)). I decided after some research to create a Userform as follows:
I believe that the userform shall make the data entry easier since i usually will have to deal with one to five thousand items that have to be coded and given notes.
i have marked the image with numbers (1-5) and want the userform to perform the following for each box/button:
1- Text Box to show the description of the item which is in Column I , starting from the row 11 and looping till row 6000
2- Combo Box, I have managed to link the Combox to the list of codes I have to enter. I have created a separate Codes sheet in my file for the codes and related short descriptions to make sure no mistakes occur while choosing or typing the codes. the sheet is as follows:
Column A Column B Column C Column D
XXX Description YYYY XXX Description YYYY
Column A is the codes in the combo box
Column D is the database code
3- Text Box to look-up the entered code in Combox (2), show the related DB code (YYYY) from Codes sheet and write it in the template sheet in Column D in the same row number as the showing description in 1 to generate all required fields in the template sheet.
4- Text Box where I have to enter the related notes which shall be written in Column AA in the same row as the showing description in 1.
5- Text Box, similar to 4 but the input shall be written in Column AB of the same row of 1
Next: shall pull the next description, It would be very convenient if the Cobobox, text boxes would all be loaded with the last entry provided since most of the consecutive items are similar
Previous: pulling the previous description and entered code, notes, queries
Close: closing the userform.
I have found some pieces of code and tutorials online showing parts of the above but couldn't pull a full code together. I would appreciate any help on the above.
My second question is regarding Vlookup, in my previously mentioned template sheet i used vlookup to lookup all required fields from teh DB, this has caused the file to grow in Size (around 10 MB) and make it a bit slower when processing, saving or opening. Are there any other better options to use other than Vlookup? would Macros and Coding help in making the processing, saving and opening faster? This is a bit critical since the file is currently at the smallest size since more sheets (usually full of conditional formatting) will be added to the file when needed.
Apologies for the very long thread and details which might be unnecessary, I tried to be as clear as possible.
I am new to the forum and macros/VB. I am working on a personal project and have looked around this forum and the web to find tips and solutions to my issue below but unfortunately I couldn't manage to get a full code together due to my minimal knowledge in Coding.
I am using Excel 2013, I have a template sheet which is linked to a database (separate sheet) containing codes that represent data and related information that fall under 6 fields. On the template sheet you need to enter the code in column D according to the item description which is in Column I. I have successfully managed to link the template sheet to my database to get and extract all the required fields using Vlookup function.
In the template sheet i need to do the data entry for three cells for every item description (Code (Column D), Notes 1 (Column AA) and Query (Column AB)). I decided after some research to create a Userform as follows:
I believe that the userform shall make the data entry easier since i usually will have to deal with one to five thousand items that have to be coded and given notes.
i have marked the image with numbers (1-5) and want the userform to perform the following for each box/button:
1- Text Box to show the description of the item which is in Column I , starting from the row 11 and looping till row 6000
2- Combo Box, I have managed to link the Combox to the list of codes I have to enter. I have created a separate Codes sheet in my file for the codes and related short descriptions to make sure no mistakes occur while choosing or typing the codes. the sheet is as follows:
Column A Column B Column C Column D
XXX Description YYYY XXX Description YYYY
Column A is the codes in the combo box
Column D is the database code
3- Text Box to look-up the entered code in Combox (2), show the related DB code (YYYY) from Codes sheet and write it in the template sheet in Column D in the same row number as the showing description in 1 to generate all required fields in the template sheet.
4- Text Box where I have to enter the related notes which shall be written in Column AA in the same row as the showing description in 1.
5- Text Box, similar to 4 but the input shall be written in Column AB of the same row of 1
Next: shall pull the next description, It would be very convenient if the Cobobox, text boxes would all be loaded with the last entry provided since most of the consecutive items are similar
Previous: pulling the previous description and entered code, notes, queries
Close: closing the userform.
I have found some pieces of code and tutorials online showing parts of the above but couldn't pull a full code together. I would appreciate any help on the above.
My second question is regarding Vlookup, in my previously mentioned template sheet i used vlookup to lookup all required fields from teh DB, this has caused the file to grow in Size (around 10 MB) and make it a bit slower when processing, saving or opening. Are there any other better options to use other than Vlookup? would Macros and Coding help in making the processing, saving and opening faster? This is a bit critical since the file is currently at the smallest size since more sheets (usually full of conditional formatting) will be added to the file when needed.
Apologies for the very long thread and details which might be unnecessary, I tried to be as clear as possible.
