Copy text to worksheet if checkbox not checked

[Willow12]

Hello experts. Hopefully an easy challenge for one of you! Any help will be greatly appreciated 😊

With reference to the attached sample file.

For any check boxes which are NOT checked on each of the visible worksheets “Warehouse 1,5 and 30, I need the text in the columns headed “Item” and “Rack” to be copied across to the balance tab.

On the balance tab -

I need the copied text lines to be grouped orderly under the heading of each of the worksheet names. “Warehouse 1,5, 30” etc. Hopefully this is clear from the sample file.

If a checkbox becomes checked on any of the worksheets then balance tab should update accordingly.

I have some flexibility to play around with the layout if the merged cells cause any problems.

The actual spreadsheet has many tabs so I’m hoping any posted solution will be easy for me to adjust and scale up to suit. I am a complete VBA novice.

Many thanks in advance.

[Joske920]

Hi,

First of all the choice to use formcontroles is not the best idea imo

it is most inportant your checkbox has the name 'Check Box ' and the number equal to the item number else you data will not be right
your sample had some doubble checkboxes on top of eachother

[Willow12]

Hello Joske920,

Firstly, thank you. I am extremely grateful for your help. Wow, that looks complicated!

So, I downloaded the updated file. Transferring the data to the balance tab works great. I copied a worksheet and renamed it and that also appeared on the balance tab, so that also seems to work really well.
I need to copy this code into my actual workbook to see if it works.

A couple of initial points -

1) If a tab is hidden I don't want it to appear on the balance sheet.
2) Currently I have to go to Macros to run the code. Can it run automatically as the each worksheet is updated? That is how the other sheet changes in my workbook currently update. Perhaps this will be the case when I copy the code across.
3) Could you add some comments within the code, to help me understand how this works and also enable me to make any adjustments. For example if I change the sheet layout or columns.
4) I may need some help with the balance layout as it appears that the design is incorporated into your code. I'll worry about this later and for now concentrate on the functionality.

Thank you once again.

[Joske920]

ok i added the comments

and the balance sheet will rebuild itself when you activate the sheet
changing the sheet every time you click a controle is not usefull because you wont see it update anyway
now it will update when you go look at the balance sheet

[Willow12]

Brilliant, thank you so much. I will review the comments added and try to follow. The sample file works great, I just need to incorporate it into my actual workbook. I'll have a go now but as it's getting late it will probably be tomorrow when I get back to you and post the results.

[Willow12]

Ok, so I've quickly copied it into my workbook. I have a couple of other tabs which are visible in addition to the checksheets and these seem to be causing an error because if I hide the extra sheets it works.

[Joske920]

i added some extra sorting

add the names of the sheets that are visible in the string, you can use ',' as seperator or any character, the full name of the sheet needs to be in it




offcourse dont add names of sheets that hold the warehouse data unless you dont want to use a certain sheet with checkboxes

[Willow12]

That has fixed that issue. Thank you!

Next problem. My fault as I didn't include this in the sample file uploaded. On the warehouse data sheets I have a couple of data validation drop down menus. These are causing the following error -

"Run-time error '1004': Unable to get the Value property of the DropDown class"

[Joske920]

hi

try this

Please Login or Register  to view this content.

[Willow12]

Fantastic, that's it!!! Thank you. I am so grateful for all your help this evening.

If you wouldn't mind, I may just need your last bit of help for the layout / formatting of the Balance sheet. I will have a look at this at tomorrow.

[Willow12]

Hello Joske920,

I’ve edited the sample file with the balance tab formatted how I would like it to show.

Top left, the orange square I will replace with a logo with the two lines of text below.

Cells with “TEXT” in with have fixed text that will never change.

“Title” will pull in the value from sheet 1 cell C9

“Balance” is the worksheet tab name

“Name” will pull in the value from sheet 1 cell C15

Colours, text size, borders shown etc are all formatted correctly to what is needed.

Would it be a big ask at this stage to transfer the data from the “Location” column of the “Warehouse” worksheets. Having done a trial run I realise I need it.

The text lines which are copied from the “Warehouse” tabs can get quite long, so I could do with the text wrapped and the cell sizes adjusting to fit the text.

Finally I need to be able to print it out on A4 sheets. So if it spills onto extra sheets I would like the formatting to continue for how many sheets may be required.

[Joske920]

Hi Willow,

im not going to bother with the different pages on your balance sheet
to much work and maybe somebody else can help you it if you really need it that way
id suggest to make the top 11 rows a print header (info on balancesheet)

The extra data is added and the layout of the balance sheet is set to what was wanted

in case of multiple pages you need to pull the print page lines on the Page Break Preview in View lint
and you have the same result

[Willow12]

Hello Joske920,

I’m not sure if maybe you misunderstood my last message when you mention different pages on the balance sheet, as what you have done here seems to be exactly what I was asking for. Your support has been absolutely brilliant and I could not have solved this without your help. Thank you, I am so grateful.

[Willow12]

Ahhh, I just found a problem.

When I start a new workbook no Warehouse tabs are visible. Then, when hiding and un-hiding warehouse worksheets in different orders, first I lose the column headers in row 11, then eventually the whole header is lost. Also, the empty cells on the balance worksheet have borders.

It seems rows 1 to 11 are always refreshed. Is there a way to prevent this?

[Joske920]

hi,

add this line

Please Login or Register  to view this content.

in ResetBalanceSheet() under lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row

[Willow12]

Hello Joske920, that were perfectly for keeping the headers fixed. Much appreciated. Is it possible to stop the empty cells below the copied data from having borders. If you hide / un-hide a warehouse tab, I think you'll see what I mean. Other than this seems perfect!

[Joske920]

i think you need to delete those rows 1 time
and it should resolve itself for the future

[Willow12]

Hi Joske920, Just tried that but still get the cell borders?

[Joske920]

did you update all the code from the last attached file?

check for differences

[Willow12]

Pretty sure I did but will check.....

[Willow12]

Yeah code seems correct. I tried on the latest file (code) you uploaded and it does the same.

[Joske920]

Hi Willow,

Yes my bad

use this instead of that sub routine

Please Login or Register  to view this content.

[Willow12]

Yes that is much better thank you

Being super critical Row 11, still has bordered cells if I hide all warehouse tabs. I played around with your code but I it falls apart when I mess with it. I tried changing the 12's in your code to 11 but it makes it worse. I then get rows 10 to 12 with bordered cells and I lose my column headers.

[Joske920]

This should make it not draw borders

Please Login or Register  to view this content.

ive added some extra on several places

[Willow12]

Wow! It is now perfect Thank you so much for dedicating your time and effort to solve this.