TM1 Forum

Hi experts,

I tried to hide the columns dynamically using below VBA code.

Private Sub Worksheet_Calculate()
If Range("E23").Value = 0 Then
Columns("E").EntireColumn.Hidden = True
Else
Columns("E").EntireColumn.Hidden = False
End If
End Sub

I found out that VBA code does not work in TM1 web. Can you please suggest any work around.


Thanks,

vins wrote:I found out that VBA code does not work in TM1 web. Can you please suggest any work around.

Why on earth would you expect VBA code to run on TM1 Web? Excel's sole purpose for TM1 web is as a "report designer". What the user interacts with in TM1 Web is not an excel file but a web page.

Either construct a report using an active form with zero suppression and move the column value to a row. Or the best you can do without changing the layout is to use conditional formatting to "hide" the column.

Thanks for your quick response.

I agree moving the column as row in an active form and suppress to zero will work. Unfortunately, its not possible to move the column to row as per business.
So, i tried looking into your another option to hide column using conditional formatting. I Google it and found it's not possible to achieve it using conditional formatting till now. Can you please provide me any link which demonstrates to hide columns using conditional formatting.

Thanks

vins wrote:So, i tried looking into your another option to hide column using conditional formatting. I Google it and found it's not possible to achieve it using conditional formatting till now. Can you please provide me any link which demonstrates to hide columns using conditional formatting.

Google can't be your answer to everything. Occasionally you do need to do some work and have an original thought ...

No, conditional formatting cannot hide a column, as in it cannot set the column width property to 0. What you can do with conditional formatting though is to set cell fill, cell borders and font colour all to the worksheet background fill colour. Hence the column will be "hidden". You already have the conditional formatting trigger condition which is the same as what it was in VBA =$E$23=0. This may be enough for the requirements in this case, it just depends whether there are other columns to the right of E.

Conditional formatting can't "hide" columns but it can clear borders and make the text color the same as the background so that the column appears empty. Combine that with some creative IF conditions that control the elements controlling the column headers and you can re-create the hidden column functionality. I've been using this strategy for years in TM1Web.

I assume you want to drill down in the columns. In that case I think the only "solution" is to have different reports that looks the same but show different column states. E.g. you start with the columns at top level and a action button which leeds the user to the second report where you show a more detailed view in the columns.