VBA to create confidential money statement for customers

[sloopbuy]

Hi, I'm looking to create a statement generator for confidential information (that is hidden in a protected tab). When a client requests their statement, my team mate will enter in the client ID, click "generate" and a statement generates. I'm looking to do two things:

• Keep a log of what statements are generated (another tab that tracks the Client ID, date generated and time generated)
• Create a new spreadsheet with the statement information and input a unique password (the password is auto generated and already in a cell in another tab in the workbook)

Does anyone have any ideas on how to create this? Thanks

 

[fadee2]

Hi sloopbuy,
Welcome to the board.

Statement generation part of your query can be achieved using formulae. But, for automated log statement and password protected statement report creation, you would need VBA.

hth...

 

[sloopbuy]

Hi sloopbuy,
Welcome to the board.

Statement generation part of your query can be achieved using formulae. But, for automated log statement and password protected statement report creation, you would need VBA.

hth...

Hi fadee2. Thanks for the advice. I was planning on using formulas for the statement and think I have a handle on this part. Any thoughts on how to set up the VBA for the two bullet points?

• Keep a log of what statements are generated (another tab that tracks the Client ID, date generated and time generated)
• Create a new spreadsheet with the statement information and input a unique password (the password is auto generated and already in a cell in another tab in the workbook)

 

[fadee2]

There are 2 ways to go with your bullet points.

First one would be to use a save and create button on the worksheet and bind a code to it, to create log report and to create a separate password protected worksheet.
But if you do not want to go through the hassle of pressing a save and create button you can use worksheet selection change event to complete the same tasks. That would be your second option.

Personally, I would go with the first option, as it give me more control over the code.

hth...