Looking for an easier way to create a Sign in sheet using Macros

**Michael Battle** · 05-25-2012, 11:53 AM

Morning everyone,

I am new to the form and not very skilled in Excel. I have learned a lot from the help feature in the program, youtube, and google but I have not been able to find what I am looking for to finish my current project. What I want to know if there is an easier way to create macros faster for 200+ rows. I don't really know how to explain it so I will attach what i have so far. I have done the first 25, but looking for an easier and faster way. If anyone knows how to, Please help me. Thanks.

**jeffreybrown** · 05-25-2012, 11:57 AM

Hi Michael & Welcome to the Forum,

Sometimes a pricture is worth a thousand words, but in the case (at least for me), can you provide a little more description of what you are trying to achieve?

**Michael Battle** · 05-25-2012, 12:19 PM

What i am trying to create is a sign in sheet where when our clients show up all we have to do is click on the green arrow and it will highlight the entire row green. I have set up the formatting but it seems like i have to create a separate macro for each row. Is there a way i can duplicate the macro but have it respond to the individual arrows. Or do i have to continue to create a separate macro for each arrow?

**dcgrove** · 05-25-2012, 12:33 PM

Wouldn't it be easier to insert a checkbox in each cell? You could eliminate the arrows and the VBA that may or may not be enabled on your end users computers.

**Michael Battle** · 05-25-2012, 12:40 PM

I thought about that but I could not figure out the Conditional Formatting. This is a must because it makes it easier for the staff to know who is checked in and who is not. Colors are very useful.

**jeffreybrown** · 05-25-2012, 12:49 PM

Hi Michael,

How about this...

Just double click on column C

Could this work for you?

**Michael Battle** · 05-25-2012, 01:00 PM

That works for me but how did you do that? And is it a way to make it just a single click?

**jeffreybrown** · 05-25-2012, 01:05 PM

I'm not sure on the single click, but I'm sure there is someway. Let me give it some thought.

Outside of a single click, maybe just put a drop down box with an "a" in the cells and then the simulated check shows up followed by the conditional formatting.

If you right click on the sheet tab >> view code >> you will see the code that produces a simulated checkbox by adding an "a" to the cell on double click and then the macro replaces the font with Marlett.

There is also a named range (ctrl + F3) which references column C and is called "mychecks". The macro uses this named range to limit the range to the defined name range.