Edit Codes to Allow For Tables Converted to Ranges

[fredfarmer]

Hello,

I would like to convert my tables to ranges but some of the coding depends on the tables. Is it possible to alter the codes to allow for the tables to ranges?

Thank you so much for helping,
Fred

[fredfarmer]

I think I can change the tables in the name manager but don't know how to alter the macros.

[fredfarmer]

I converted the Tables 1, 2, 4 to:

AFRsInputParts
AFRsDB
AFRsParts

Now can I just replace the table references to these named ranges?

[YasserKhalil]

I have downloaded the file and there are a lot of modules.. What module has the code you need to modify? and what you are trying to achieve?

[fredfarmer]

The modules that are affected by Table1, Table2, and Table4. The other tables aren't affected by the codes. I will eventually change those to ranges also.

My objective is to try to share the file here on our network. With tables I was made aware that it cannot be shared.

Thank you

[fredfarmer]

The workbook is functioning properly with the tables in place but I want to be able to share it on our network. That was my intention from the start. I was made aware that with tables it cannot be shared.

Thank you

[AliGW]

Can you share the file via OneDrive (Sharepoint)? That way you will be able to retain tables.

[fredfarmer]

No the owner of the company will not allow it sorry and thanks for the suggestion Ali. It needs to stay on our local network.

[AliGW]

That's unfortunate.

[fredfarmer]

Yes I know

[AliGW]

Does this help at all?

https://bettersolutions.com/excel/na...s/vba-code.htm

[fredfarmer]

I am struggling with code but trying to learn and thank you for your help Ali. I do pretty well with formulas and seem to understand them better.

[AliGW]

But this is in the VBA section ...

[fredfarmer]

Yes that is why I need the help. Roel did so much work on the codes and he seems to understand how everything works. He sent me a private message stating that he will have a chance in the next few days to possibly change the codes.

[AliGW]

OK - that's useful to know. It would have been helpful if he'd posted that here so that we knew someone was onto it.

[fredfarmer]

Very sorry about that. I didn't intend to be a bother for you.

[AliGW]

It's no bother - but if someone is going to be looking at it, it's useful to know that. This is a community, remember - keeping everyone fully informed is key to it working properly. Anyway, let's wait now until Roel posts here.

[fredfarmer]

Okay in the future I will mention if I am getting help if this happens again.

Cheers!

[AliGW]

Just for the record, you may (both) wish to remind yourself (yourselves) of forum rule #8:

Do not private message forum moderators or members for Excel help. (A, Z)

Administrative Note:

All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

[fredfarmer]

Okay I will thanks so much.

[Roel Jongman]

I had a look at your problem.. It took some changes and testing.

Also I rearranged the codes form 15 separate modules (which made it hard for me to find the relevant codes) to 5 key modules each with 2 or 3 codes

those modules are
modFormLoadUnload - handles all loading and unloading (clearing) the inputform on sheet AFRsInput
modFormSave - handles the save or update of the AFR record in AFRsDB and AFRsParts
ModFormSupportButtons- has some codes for new AFR and reopen AFR buttons
ModPrintorPreview - holds all the codes for the print and preview buttons
ModPWSecurity - holds all codes that handles password protected features

Posting the codes in this answer is counter productive as about 8 codes have been changed to handle the sheet without tables.
3 codes have been removed these ar
- ClearPartstable, the handling of the partstable in range O:Q on AFRsInput is now part of the AFRsInput_DataArea named range
- AddMoreRows
- Delete Rows
last two because there is no table to add rows to or remove rows from.
The parts table is now a preformatted list in the range O3:Q23, 21 parts seem enough to handle all repairs. I did not see added value in unformat and reformat the partstable range for loading and unloading each AFR.

As anohter improvement I changed the sheet protection handling. Before the AFRsInput sheet was unprotected and protected for each change. Now I have created two short macro's in the new ModPWSectrity
Const formprotect = "pass"
Const Clerkpw = "clerk"
Const NewAFRpw = formprotect

Please Login or Register  to view this content.

The FormLock code has an addtional parameter UserInterFaceOnly:=True this allows for all codes to be excecuted while the sheet protection is on.
to make sure that the sheet protection is always on there is a new event macro worksheet_open in the Thisworkbook module

very short code

Please Login or Register  to view this content.

this will make sure the sheet is locked. most of the time locking on opening is redundant but a good safety measure.
In theory the unlock code is not needed but I think it is always a good measure to have those codes available in case you later decide to write (more complex) codes that will be better to handle with unlocked worksheet again.

So please look at the attached file and test if everything still works as intended in the "table-version"

[fredfarmer]

Thanks Roel I did find one issue in the New AFR:

Please Login or Register  to view this content.

[Roel Jongman]

oops, small issue with sheet ranges..

Please Login or Register  to view this content.

[fredfarmer]

Thank you so far so good. I will test more in the next few days.

I appreciate the fantastic help and support and wish I could find a way to pay it forward.