One VBA Userform Feeding a selected Workbook

[S18bsa]

Hi all.

Our business is moving away from a third party that looks after our sickness. Whilst we await another (possibly), I have been tasked with creating an interim solution. What the powers that be have asked for, is a central user form that can populate a selected Workbook. As we have separate departments, they want the Head Of each department to have their own Log/workbook and the central admin staff to populate it from one Userform. ie Planning Department is selected and the data populates that workbook only. There are 5 different departments. I would have done it with a Master sheet and power query it into the departments but they don't want anyone other than the Selected Head of to see their own sensitive information. There will be a master that brings all the data together for the Directors.

With my limited VBA, I can do this is in the same sheet, but not sure how to do it with different workbooks.

I guess the first question is, can it be done?

 

[dmt32]

Hi
welcome to forum
What you are asking can be done in a limited fashion but will prove to be a lot of work - Also, you should understand that Excel is not a secure application & if you are intending storing sensitive personal data then I would suggest that you avoid using it for that purpose & seek out other products that would better match your need.

For example - If your Employer is using Office 365 you could investigate if templates available in PowerApps like this: How To Setup an Office 365 Holiday and Time Off Request App Using Power Apps | SlashAdmin \ Life in IT


This is just a suggestion of an alternative product that may or may not, be suitable for what you want to do - others here may have further thoughts to guide you.

Dave

 

[S18bsa]

Hi
welcome to forum
What you are asking can be done in a limited fashion but will prove to be a lot of work - Also, you should understand that Excel is not a secure application & if you are intending storing sensitive personal data then I would suggest that you avoid using it for that purpose & seek out other products that would better match your need.

For example - If your Employer is using Office 365 you could investigate if templates available in PowerApps like this: How To Setup an Office 365 Holiday and Time Off Request App Using Power Apps | SlashAdmin \ Life in IT


This is just a suggestion of an alternative product that may or may not, be suitable for what you want to do - others here may have further thoughts to guide you.

Dave

Hi Dave,

Many thanks for your response and thank you for the welcome.

I did think that it would be a bit of work, as normally a trawl around the Tinterweb can usually throw up something close to what you require, but in this instance - nothing!

Unfortunately, our network is not connected to the cloud as Powerapps would have been perfect.

Thanks again

Andy

 

[dmt32]

If you want to understand userforms there is a free guide here: How to Create Excel UserForm for Data Entry
but they are a lot of hard work as you have to write every single line of code to make it work in manner required. Also as already mentioned, Excel is not secure & best avoided if application is to be used to hold sensitive data.

If you have MSAccess on your desktop then perhaps exploring this to see if it is something better suited to your need & an application you could work with?

Dave