Legislative Compliance Database

I am creating a legislative compliance database. My source is https://www.legislation.nsw.gov.au/#/browse

The purpose of the database is to identify the State legislation that local government (Council) has to comply with. The majority of legislation will not be relevant to local government. However, some will be relevant to specific or all local government Councils. A Legislative Compliance Register is mandated within the Local Government Act 1993 No. 30.

I initially thought to download lists from the PDF files that can be created on the site. However, this proved problematic as it was very difficult (if not impossible) to transfer PDF data into an excel worksheet. The other difficulty was that page 2 of the PDF files created didn't contain hyperlinks. It simply proved easier to cut and paste the information into the Excel worksheet. There were 875 records.

The data I cut and pasted was: (a) the legislation name (hyperlinked), (b) the long title, and (c) the object or purpose of the Act (when available). I also created a unique number for each record (001 to 875).

I will add a number of other fields at a later date, e.g., category. A category may be Child Protection or Aged Care, Water, Sewerage.

Another worksheet will contain those sections of the legislation that local government specifically needs to comply with. I may even enter delegations, e.g., the position responsible for compliance or enforcement of those sections of the legislation. Delegations only use positions and not names.

I have created a Table for the data, and a dynamic name range ("legislation") for the Acts.

What I would like is a Userform (mainly the code) that enables a user to start typing the legislation name and have the Act(s) with those character(s) appear in a list. I would like the user to select an entry (perhaps by radio button) from the list and then have another form display the entire record, i.e, unique number, legislation name, long title and objective. Note the objective cell height could be up to 409 (the Excel cell maximum). I would now like the second form to have an entry for (say) "Category" which can be updated. None of the other fields can be modified. The userform should return to the initial search field and display the list found to date.

I have spent hours looking for a userform and code that will enable me to do this. The userform is not that difficult, it is the code behind it that I am having difficulty with.

I would appreciate any assistance.

Sorry for the delay in responding.

The structure of the database is simple. There is:

- a Unique Identifier: 001 to 999 (there are just over 870 pieces of legislation)

- the name of the the legislation (which is currently hyperlinked)

- the status (currently only populated with ("in force"). In future it might include "Repealed".

- the long title

- the objectives, purpose or principles of the legislation, and

- a legislative category. e.g., Child Protection

No. 0001

Aboriginal Housing Act 1998 No 47 In Force

Long Title

An Act to constitute the Aboriginal Housing Office; to confer functions on the Aboriginal Housing Office relating to housing assistance for Aboriginal people and Torres Strait Islanders and for other purposes; to amend certain Acts; and for other purposes. "

The objects of this Act are as follows

(a) to ensure that Aboriginal people and Torres Strait Islanders have access to affordable and quality housing,

(b) to ensure that such housing is appropriate having regard to the social and cultural requirements, living patterns and preferences of the Aboriginal people or Torres Strait Islanders to whom the housing is to be provided,

(c) to enhance the role of Aboriginal people and Torres Strait Islanders in determining, developing and delivering policies and programs relating to Aboriginal housing,

(d) to ensure that priority is given, in providing housing assistance for Aboriginal people and Torres Strait Islanders, to those individuals who are most in need,

(d1) to ensure that the available supply of housing under this Act is shared equitably among Aboriginal people and Torres Strait Islanders who are most in need,

(e) to increase the range of housing choices for Aboriginal people and Torres Strait Islanders so as to reflect the diversity of individual and community needs,

(f) to ensure that registered Aboriginal housing organisations are accountable, effective and skilled in the delivery of Aboriginal housing programs and services,

(g) to ensure that the Aboriginal Housing Office’s housing programs and services are administered efficiently and in co-ordination with other programs and services that are provided to assist Aboriginal people and Torres Strait Islanders,

(h) to encourage the sustainable employment of Aboriginal people and Torres Strait Islanders in the delivery of Aboriginal housing assistance.

Legislative Category

Indigenous Compliance (example)

I'll provide a possible Userform format shortly.

P.S. If this works out all right I propose that any other Councils who find it useful can use it too.