Scribd
Upload
188 views18 pages

Excel Chatgpt

The document describes using ChatGPT to automatically hide or show sheets in an Excel workbook based on the user. It provides instructions to create a macro that will run on workbook open to check the user name in a named range, and then loop through a table to hide or show sheets as specified. The goal is to hide data sheets for most users but show them for the workbook owner, providing automatic security without complex setup.

Uploaded by

Franklin PS
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
188 views18 pages

Excel Chatgpt

The document describes using ChatGPT to automatically hide or show sheets in an Excel workbook based on the user. It provides instructions to create a macro that will run on workbook open to check the user name in a named range, and then loop through a table to hide or show sheets as specified. The goal is to hide data sheets for most users but show them for the workbook owner, providing automatic security without complex setup.

Uploaded by

Franklin PS
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 18

EXCEL AUTOMATION

WITH CHATGPT

Automatic Sheet Hider

RICHARD NERO
MANAGING LARGE
WORKBOOKS CAN BE ROUGH

Sometimes you don't want


your users to see all that data

So you spend ages hiding data


sheets before sharing

RICHARD NERO
What if your workbook was
smart enough to hide
those data sheets..

...automagically!

Matt Brattin
The Daily Analyst - Helping you Excel Daily

RICHARD NERO
THE CHALLENGE

Hide data sheets for anyone


opening the workbook

But unhide them for you!

RICHARD NERO
YOU COULD ENTER THE
TIME VORTEX

Hours searching YouTube

Hours searching forums

Hours getting nowhere

OR MAYBE
...just ask ChatGPT

RICHARD NERO
THE SETUP
Make a single cell named range called 'User'

Create a 'SheetList' table


with sheet names and
what to do with them

RICHARD NERO
THE PROMPT

RICHARD NERO

Please write a script that fires when an Excel


workbook is opened.

The User Name should be written into the named


range 'User' and should be checked to see if it is
"Richard.Nero".

If it is, all sheets should be unhidden with a pop up


saying "Welcome back Richard!"

If its not, loop through the Excel table 'SheetList' in


the 'Control' sheet. Hiding or showing each 'Sheet
Name' as per the 'Status' column. When finished
display a pop up saying "File Secured!"

RICHARD NERO
THE RESPONSE

CHATGPT

RICHARD
WHAT DO YOU DO WITH ALL
THAT LOVELY VBA CODE?

Hit ‘Copy code’ in ChatGPT

Go into Excel and press

This takes you into VBA world

RICHARD NERO
Double click 'ThisWorkbook'

This will create a


special module for you

This should be 'Open'

Paste code here

RICHARD NERO
Now you this a special macro

It is an event

This means it is triggered


by something

RICHARD NERO
Events are cool!

They can trigger....

..when you save a file

..when you open a file

..when you close a file

..when you double click

..when you calculate a sheet


RICHARD NERO
But for now ours will
launch whenever the
workbook is opened

RICHARD NERO
So Karen from HR comes
along and opens the file.....

RICHARD NERO
I open the file and..

RICHARD NERO
You have just created a
user dependent file

You deserve a pay rise, a


promotion or perhaps both

RICHARD NERO
If you enjoyed that please repost
so your network can enjoy it too

Follow for more Excel +


ChatGPT automation tips

Richard Nero
Finance Systems Consultant |
Excel Trainer | Mentor | SQL |
Python | Power Platform Pro

Let me know your


thoughts in the
comments

You might also like