Amending the functionality of an old Form

**swaatacba** · 07-19-2023, 06:32 AM

Many years ago I built a form that allowed the user to compare a Master list to a Slave and print out the differences between them.

MasSlavForm.png

The functionality of the form allowed the user to identify a cell by its address and evaluate its 'CurrentRegion'. I now want to alter the mechanism for finding suitable regions for comparison.

In this new environment all suitable regions will have standardised local names, like 'FormulaList' or 'MapKey'. I propose using these local names to identify the regions for comparison.

I would like advice on how I might amend the Form without having to rebuild it from scratch.

**PCI** · 07-19-2023, 02:33 PM

An Excel file and code is mandatory ...!

**swaatacba** · 07-20-2023, 04:48 AM

Originally Posted by PCI

An Excel file and code is mandatory ...!

I apologise for taking so long to respond.

The original form (fmCompList) and its associated code is contained in an Add-In that I built about 20 years ago. This can be downloaded.

ACBA Tools

I was looking for advice on how to replicate the form employing local named ranges. You will appreciate that I am perfectly capable of rebuilding the the form from scratch.

However there are several connected coded elements to the form and I was hoping for advice (not code) on a shortcut to a re-build.

**AliGW** · 07-20-2023, 04:51 AM

To be absolutely clear: are you asking for help to adapt proprietary code from the add-in? Because if you are, then the licence agreement (do you have one?) may prohibit this.

**Greg M** · 07-20-2023, 05:06 AM

Hi there,

WOW! Impressive AddIn - more than 15,600 lines of actual code!

Knee-jerk comment as (perhaps not surprisingly!) I haven't gone through all of the code in detail.

Would it be useful to replace the various "List Range" TextBoxes with ComboBoxes whose dropdown lists are automatically (Worksheet_Activate event) populated with the Named Ranges contained in the active worksheet?

Sorry, I know that this is just a comment rather than a chapter-and-verse solution.

Regards,

Greg M

**swaatacba** · 07-20-2023, 05:53 AM

Originally Posted by AliGW

To be absolutely clear:

ACBA (Allen Computing and Business Analysis) is, or was, me. It is entirely my own code. No licence is required, but if it were the only person authorised give it is me.

**swaatacba** · 07-20-2023, 06:08 AM

Originally Posted by Greg M

Would it be useful to replace the various "List Range" TextBoxes with ComboBoxes ...?

Thanks Greg. Yes, I'm hoping that will work. Typically there are ancillary issues that need to be taken into account - for example not all local names refer to lists, so I don't want to include them.

Stephen

**AliGW** · 07-20-2023, 06:10 AM

Originally Posted by swaatacba

ACBA (Allen Computing and Business Analysis) is, or was, me. It is entirely my own code. No licence is required, but if it were the only person authorised give it is me.

Thank you for clarifying. Not sure if I should or could have known that, but good to know!

**Greg M** · 07-20-2023, 07:09 AM

Hi again Stephen,

for example not all local names refer to lists, so I don't want to include them

Ok on that. I usually use prefixes to denote what a Defined Name refers to, e.g.:

tblCategories ...... denotes a table
ptrUserNames ..... denotes a Range (ptr = Pointer)
forNoOfNames .... denotes a formula
iMAX_NAMES ...... denotes a constant integer value

An approach like that might help you to populate dropdown lists on a more selective basis - just another comment!

Regards,

Greg M

**swaatacba** · 07-20-2023, 07:24 AM

Thanks Greg. I'll certainly take that on board.

Stephen

**Greg M** · 07-20-2023, 07:36 AM

Hi again Stephen,

Many thanks for your very prompt feedback and also for the Reputation increase - much appreciated!

You're welcome - glad you found that suggestion useful.

This seems like a very interesting project and I'd be glad to know how you're progressing with it, so please send me a private message if you're posting anything else related to it.

Best regards,

Greg M

**ByteMarks** · 07-20-2023, 08:03 AM

use prefixes to denote what a Defined Name refers

It's a practice know as Hungarian Notation
https://en.wikipedia.org/wiki/Hungarian_notation#

**Greg M** · 07-20-2023, 08:50 AM

It's a practice know as Hungarian Notation

Indeed so, and one which I use extensively!

Greg M

Amending the functionality of an old Form

LinkBack

Thread Tools

Rate This Thread

Display

Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Re: Amending the functionality of an old Form

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Form Control passed as a parameter loses functionality (.Clear method)

Excel Spreadsheet with VBA and Form Controls; Full functionality in Android with Polaris

Amending One Field from Another

Help with VBA amending

Search form functionality with combo box

Amending Sub totals

[SOLVED] saveto or Submit Form functionality in Excel

Bookmarks

Bookmarks

Posting Permissions