VBA changing a table loop to a single pass / TableStyle

[BananaKing]

Hello everyone,

after a quite long research I decided to register and ask the experts here. I'm more or less completely new to VBA, but I'm sure I'm in the right place now..

My question might sound very amateurish, nevertheless I couldn't find a solution:
I've found a VBA code for imitate a dark mode in Excel [ExcelDarkMode/DarkMode.vba at main · stu0292/ExcelDarkMode]. I could run it, but it affects all tables in a spreadsheet - I just want it to change the active one only.

I've found out the function for the loop starts at line 277 (278) and ends at line 286 and will be called from different parts of the whole code. So, I assume best would be to adjust these lines here...

I've also found a solution to the fact, that a sheet could be named individually and the name itself should not be hardcoded:

Excel VBA - Select Active Table in Active Sheet in Active Workbook?

I am trying to write a Macro that would select the Active Table in the Active Sheet in the Active Workbook for data stored in "C3:H9". I know the following VBA references can be used: "Application.ActiveWorkbook.ActiveSheet.ActiveCell" can be used to refer to the ActiveCell as mentioned in Link...

www.mrexcel.com

I guess, why here a 'function' (instead of a 'sub') is used has its reasons, but as I said, I'm pretty new to the topic, so I more or less accept that fact for now.
Leaving off the 'For Each's and 'Next's would just seem to simple - anyway doesn't work..

How would a simple solution look like?
Many thanks in advance!

PS. Doing the research I just gained a lot interesting information and I might start learning and working with VBA maybe, at least I'm very motivated what could be achived with it! But for now I simply need to protect my eyes and continue with some other stuff in Excel... on a new spreadsheet the 'dark mode' VBA affecting all sheets does its jobs pretty well, but I'm working with some files, where not everything should be altered. I hope I provided you with all necessary information and did not forget anything!

 

[John_w]

it affects all tables in a spreadsheet - I just want it to change the active one only.

Try this macro:

Public Sub Set_Active_Table_Style()

    Dim activeTableName As String
    
    activeTableName = ""
    On Error Resume Next
    activeTableName = ActiveCell.ListObject.Name
    On Error GoTo 0
    
    If activeTableName <> "" Then
        ActiveSheet.ListObjects(activeTableName).TableStyle = "TableStyleDark1"
    Else
        MsgBox "The active cell, " & ActiveCell.Address(False, False) & ", is not in a table"
    End If

End Sub

 

[BananaKing]

Hello John_w,

thank you for your reply! As I'm a bloody beginner, I need a little bit more help please... where do I put this code into? Should I create a new module (as you mentioned I should try that macro) or should it be part of the whole dark mode code? (if yes, where exactly or should it replace the function that does the loop through each table in the workbook mentioned above?)

I very much appreciate your help!

 

[John_w]

You shouldn't need any of the dark mode code you linked to, because I've used one of the built-in table styles.

Copy and paste my code into a new regular module - see How to Add Macro Code to Excel Workbook for help.

 

[BananaKing]

I really like it if it's efficient, but could this short code of yours preserve the functionality of the one referenced above (but just affecting 1 table instead of all)? If it should be a macro in a new module, would it be independent from the other module (with the dark mode code) and could it be toggled then too? It seems it would assign a specified value (= "TableStyleDark1"), but with a toggle functionality wouldn't there be something like a variable instead, where the value could vary.. (= styleName)?

 

[John_w]

The dark mode code toggles between dark and light for all tables and all Excel cells. Do you want to toggle between dark and light for only the active table (not Excel cells)? If so, try this (complete code, which includes some of the dark mode code):

Public Sub ToggleDarkMode_ActiveTable_Only()
    
    Dim activeTableName As String
    
    activeTableName = ""
    On Error Resume Next
    activeTableName = ActiveCell.ListObject.Name
    On Error GoTo 0
    
    If activeTableName <> "" Then
    
        Application.ScreenUpdating = False
        
        ' Create a custom property to save state of Dark/Light mode in the workbook
        Dim flag As String
        flag = "DARK_MODE_0292"
        If Not CustomPropertyExists(flag) Then
            ActiveWorkbook.CustomDocumentProperties.Add Name:=flag, Value:=0, LinkToContent:=False, Type:=msoPropertyTypeNumber
        End If
        
        ' Toggle state based on flag
        If ActiveWorkbook.CustomDocumentProperties(flag).Value = 1 Then
            ' Dark to Light
            ActiveWorkbook.CustomDocumentProperties(flag).Value = 0
            LightMode activeTableName
        Else
            ' Light to Dark
            ActiveWorkbook.CustomDocumentProperties(flag).Value = 1
            DarkMode activeTableName
        End If
    
        Application.ScreenUpdating = True
    
    Else
    
        MsgBox "The active cell, " & ActiveCell.Address(False, False) & ", is not in a table"
    
    End If

End Sub


Private Sub DarkMode(activeTableName As String)
    'Set active table to this dark table style
    SetTableStyle activeTableName, "TableStyleDark1"
End Sub


Private Sub LightMode(activeTableName As String)
    'Set active table to this light table style
    SetTableStyle activeTableName, "TableStyleMedium9"
End Sub


Private Sub SetTableStyle(tableName As String, newStyle As String)
    ActiveSheet.ListObjects(tableName).TableStyle = newStyle
End Sub


Private Function CustomPropertyExists(propName As String) As Boolean
    Dim wb As Workbook
    Dim docProp As DocumentProperty
    Dim propExists As Boolean
    propExists = False
    Set wb = Application.ActiveWorkbook
    For Each docProp In wb.CustomDocumentProperties
        Debug.Print docProp.Name
        If docProp.Name = propName Then
            propExists = True
            Exit For
        End If
    Next
    CustomPropertyExists = propExists
End Function

 

[BananaKing]

Okay, I tried out, if I could achive what I wanted... I think we might have a missunderstanding. What I called table might be something different, what it technically really is. I want the active sheet to be darkend/lighted (toggle function) - in my mothertongue Excel names them what could be translated to 'table1,2,3..' by default, so that's why I maybe did not understand what is meant by a table and did express myself completely missleading. Sorry!

To clarify - a personal Excel file with individual data consists of more spreadsheets, which are shown at the bottom left side in Excel's socalled leaf bar - they are named 'Sheet1,2,3..' in English. They consist of cells, so I think that's what I want to be adjusted (and that's why I thought the loop function of the origin code would do the trick). I only want e.g. Sheet5 to be changed (the macro should just apply the change to the currently active sheet).

The linked code above (in my first post) does its job for all the spreadsheets, but for reasons I need that effect to be added only to a single spreadsheet I'm actively in (using the origin code of dark mode altering all spreadsheets could have a negative effect, if there is a layout in at least one of the spreadsheets and I want to avoid a change there).

A workaround would be to run the origin macro, when all other spreadsheets/cells, that should not be altered, are locked, but that takes time and if there are already cells that are locked/unlocked by design, that would destroy the spreadsheet too.

Your code seams complex (at least to me as a noob) and must have taken some time... I'm very sorry that I've wasted your time so far by expressing myself in an unclear way! But nonetheless I appreciate your effort!


PS. The linked dark mode code is at least the best opportunity I found, to have something to protect my eyes but have it available more or less independently from an individual file (although it alters the individual file in the end). E.g. assigning a prepared darkened background each time seams more cumbersome (and I anyway do not know how to accomplish that via macro - and fonts and cell grid would still be stressful). But I'm very open for better suggestions having a practical effect of a dark mode.

 

[Skyybot]

If you're looking for Dark Mode I believe that you can click on File then click Account and choose Dark Grey under Office Theme. Not sure about 2010 version.

 

[Skyybot]

If you're looking for Dark Mode I believe that you can click on File then click Account and choose Dark Grey under Office Theme. Not sure about 2010 version.

Oh WOW! Nevermind, that's worse. Window is dark but cells are still white. Disregard my suggestion.

 

[Skyybot]

Try this to see if the cell color works. If so, we can work on the cell content text color and cell border color. Once everything is settled, we can work on making work for only workbooks that you open and will stay normal for other users.

Private Sub Workbook_Open()
For i = 1 To ThisWorkbook.Sheets.Count
    ThisWorkbook.Sheets(i).Cells.Interior.Color = RGB(80, 80, 80)
Next i
End Sub