Question: Should I put code into the workbook module or work sheet module?

[ckdragon]

Hi there

I currently have code in the module section which formats the sheet when call upon (via a button on said sheet)

The problem is, is that once I have pressed said button and the sheet is formatted, if I change any of the cell values the formatting doesn't change. I have multiple macros that link to that one button and if I repress it is stuffs up some other things in the sheet.

I am just wondering if I transfer the VBA formatting code I have from modules into the workbook module or the worksheet module if the formatting will automatically update when values are changed?

If so, is there any specific code I would need to include to action that?

Thanks!

 

[6StringJazzer]

I currently have code in the module section

Don't know what you mean by "the module section". A workbook can have sheet modules, standard modules, ThisWorkbook module, userform modules, class modules, but there is no "module section".

if I change any of the cell values the formatting doesn't change.

I'm not sure what you mean here. You just ran a macro that formats the sheet. What is it that you want to change afterwards just because you changed a cell value? What is your use case?

The short answer is probably that you should Worksheet_Change in the sheet module for this sheet. It will run anytime you change any value on that sheet.

In general you should use the ThisWorkbook module for any workbook-level events, worksheet modules for worksheet-level events and any code that applies to a specific sheet, and standard modules for code that doesn't fit into one of the above. This is very general rule of thumb and judgements need to be made for each design.