Add functionality to automatically highlight selection in VBA menus – Microsoft Excel
- or -
Post a project like this1741
£80(approx. $100)
- Posted:
- Proposals: 4
- Remote
- #2447030
- PRE-FUNDED
- Awarded
Description
Experience Level: Intermediate
I have an old accounting bank analysis tool originally from 1999 and built in excel using VBA macros, it has been updated in a previous project to address several issues that were appearing in newer versions of excel.
The issues have all been solved but in the process the sheet has lost the following abilities:
1. Automatically highlight menu selections from the row above when entering monthly transaction data in the Account Details, Date and Ref fields
2. Automatically highlight the corresponding default “Code” number associated with the account selected in that row (One default code is assigned to each account in the “Add/Amend A/c Nos” section of the workbook)
The ability to highlight the selection from the above row in the menu and then either press enter to choose selection or use the arrow keys to make another menu item selection significantly increases efficiency when entering many transactions into the program.
One of the original issues was that numlock was enabling at various times e.g. when data was being entered into the amount column and when menus were clicked, I think that the fix for this issue conflicted with the automatic highlighting in the menu. The developer at the time was attempting to solve the problem but unfortunately, I have been unable to make contact for the last months.
Using the sheet is not initially obvious, to recreate data entry process, use the following steps:
Main Menu --> Enter/View Transactions --> Select a Month --> Select cell in row below last data entry --> Use insert button if no blank rows are available --> Use “Data” menu to insert account numbers --> Use “Data Entry” menu to fill in remaining data.
For the project to be complete I would like items 1 and 2 above to be working cleanly in the “Updated 2019” sheet whilst not bringing back the numlock or any other issues.
Please see attached images from the original sheet showing how the menus should work. In the updates sheet, none of the menu items are automatically highlighted.
I can send the spreadsheet on request and can also include the original spreadsheet for insight of how the functions were working before.
The issues have all been solved but in the process the sheet has lost the following abilities:
1. Automatically highlight menu selections from the row above when entering monthly transaction data in the Account Details, Date and Ref fields
2. Automatically highlight the corresponding default “Code” number associated with the account selected in that row (One default code is assigned to each account in the “Add/Amend A/c Nos” section of the workbook)
The ability to highlight the selection from the above row in the menu and then either press enter to choose selection or use the arrow keys to make another menu item selection significantly increases efficiency when entering many transactions into the program.
One of the original issues was that numlock was enabling at various times e.g. when data was being entered into the amount column and when menus were clicked, I think that the fix for this issue conflicted with the automatic highlighting in the menu. The developer at the time was attempting to solve the problem but unfortunately, I have been unable to make contact for the last months.
Using the sheet is not initially obvious, to recreate data entry process, use the following steps:
Main Menu --> Enter/View Transactions --> Select a Month --> Select cell in row below last data entry --> Use insert button if no blank rows are available --> Use “Data” menu to insert account numbers --> Use “Data Entry” menu to fill in remaining data.
For the project to be complete I would like items 1 and 2 above to be working cleanly in the “Updated 2019” sheet whilst not bringing back the numlock or any other issues.
Please see attached images from the original sheet showing how the menus should work. In the updates sheet, none of the menu items are automatically highlighted.
I can send the spreadsheet on request and can also include the original spreadsheet for insight of how the functions were working before.
Christopher S.
100% (5)Projects Completed
5
Freelancers worked with
5
Projects awarded
29%
Last project
4 Oct 2020
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
As you've noted with each new version of Excel certain VBA functions are changed or deprecated altogether making it necessary to make adjustments to the code.
I see in your code snippet an IF statement pertaining to the VBA version the macros is being run on.
Question: Will your sheet need to run on more than one version of Excel for normal operation? For example 2 or more people using the tool on different machines with different versions of Excel?
Thank you....Christopher S.02 Jul 2019Yes, the sheet is accessed from multiple machines with different versions of excel. I would need it to work in Excel 2010 and newer
823383
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies