Macro count items in sheet2 and provide count in embedded label in sheet1

I am trying to create a simple count macro that will return the count to an embedded label on Sheet1.

The count is essentially all Items (i.e cell that maintains a value) in column A of Sheet2 starting at Row8

If there is none then count = 0

I plan on incorporating this into my workbook open code.

thanks for any help in advance.

Assuming you are using a form control label identified and "Label 1". This worked on a tested workbook.

Please Login or Register  to view this content.

Originally Posted by stnkynts

Assuming you are using a form control label identified and "Label 1". This worked on a tested workbook.

Please Login or Register  to view this content.

Stnkynts,

Thank you for the reply. I tried using the above code adjusted with specifics to my workbook. I have used a Label ActiveXcontrol from the developer tab.

Please Login or Register  to view this content.

Originally Posted by stnkynts

Which is why it did not work. Below will work for an ActiveX label

Please Login or Register  to view this content.

Thanks for the reply. Unfortunately I was not able to get it to work with the change. I have even tried adding a new label without formatting at all and running it and still cant seem to get it to function. Any thoughts as to the problem?

Submit your workbook because both codes worked just fine for me when tested.

(PSM-07-03) Low Risk Contractor Database v2.xlsm

Please see attached.

It works just fine for me on your database. I run the code (independently of Workbook_Open) and a value of 3 is put into the Total Variances box under the "Database Summary" on the "Home" sheet. I don't know what is going wrong for you. Best of luck

Did you try operating it using the workbook open function...do you feel that is the problem?

Hmmm. There is some weird stuff going on with your sheet. After multiple testing, it would work, then I would close the sheet, open it back up, and it wouldn't work. In addition, your built in "Dashboard" macros to select the different sheets were on the fritz too. Sometimes they would work, sometimes the most simple single line of code would error for no reason. Sorry but I don't know what to tell you. Best of luck.

Stnkynts,

I was able to get it to function as well today. Just randomly started working. I am not sure if you say the dashboard macros and this seem to have issues if it might have something to do surrounding "objects" in general and using macros on them.

I am going to try running with it as it seems the code itself is accurate to get the desired results when it works. I would then like to inquire how I would make this structure conditional? In a sense to make the count the same but only count items with say "X" in column E and "PENDING" in column W? Would this require much modification as I can start a new thread if so.

Originally Posted by stnkynts

Hmmm. There is some weird stuff going on with your sheet. After multiple testing, it would work, then I would close the sheet, open it back up, and it wouldn't work. In addition, your built in "Dashboard" macros to select the different sheets were on the fritz too. Sometimes they would work, sometimes the most simple single line of code would error for no reason. Sorry but I don't know what to tell you. Best of luck.

Stnkynts,

I was able to get it to function as well today. Just randomly started working. I am not sure if you say the dashboard macros and this seem to have issues if it might have something to do surrounding "objects" in general and using macros on them.

I am going to try running with it as it seems the code itself is accurate to get the desired results when it works. I would then like to inquire how I would make this structure conditional? In a sense to make the count the same but only count items with say "X" in column E and "PENDING" in column W? Would this require much modification as I can start a new thread if so.