Setting Function Boolean var to False or True

Question

I have the Function below that is suppose to set the property for the items below to True or False. I am able to do it on all properties except for this one:

.Application.ExecuteExcel4Macro("Show.ToolBar(""Ribbon"", False)")

When I try to replace False with my parameter boolStatus, and I run the function, it will not change. So I gave up and just left it as False, but I really needed to change from False to True and vice versa otherwise my function is only half working. As it stands right now, I would have to create a second function where I would set the line to True, I am pretty certain that is double work.

Module sheetView

    Public xlWB As Excel.Workbook = CType(Globals.ThisWorkbook.Application.ActiveWorkbook, Excel.Workbook)
    Public xlWS As Excel.Worksheet = CType(xlWB.ActiveSheet, Excel.Worksheet)

        Function ViewSheets(boolStatus As Boolean) As String

        'This function selects a dashboard and hides
        'the gridlines, headings, tabs and toolbar.

        '@parameter sheetName, calls the sheet to be selected
        '@parameter status, sets the objects to view or hide


        With xlWS

            .Application.ScreenUpdating = False

            'Disable the following controls
            .Application.ActiveWindow.DisplayGridlines = boolStatus
            .Application.ActiveWindow.DisplayHeadings = boolStatus
            .Application.ActiveWindow.DisplayWorkbookTabs = boolStatus
            .Application.DisplayFormulaBar = boolStatus
            .Application.DisplayStatusBar = boolStatus
            .Application.ExecuteExcel4Macro("Show.ToolBar(""Ribbon"", False)")


            .Application.ScreenUpdating = True

        End With


        Return ""

    End Function

End Module

What am I doing wrong with that line?

Perhaps something like .Application.ExecuteExcel4Macro("Show.ToolBar(""Ribbon"", " + boolStatus + ")")? — Tim
– Tim, Commented Aug 15, 2013 at 2:58

Vivek Jain · Accepted Answer · 2013-08-15 02:55:24Z

3

You may try the following:

.Application.ExecuteExcel4Macro("Show.ToolBar(""Ribbon"", " + boolStatus.ToString() +")")

Or something similar

Please check this code for exact VB.Net syntax

answered Aug 15, 2013 at 2:55

Vivek Jain

3,8856 gold badges33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Over a year ago

Same thing I was thinking. Syntax looks correct - though & might work (or could even be needed - VB.NET gets confusing to me on that sometimes).

Tim · Accepted Answer · 2013-08-15 03:22:18Z

2

I think the issue is that you're passing in a string to the macro function, so you need to concatenate the boolStatus with the string, like this:

.Application.ExecuteExcel4Macro("Show.ToolBar(""Ribbon"", " + boolStatus.ToString() + ")")

Your previous code was probably adding the variable name into the string, hence the issue. For example, if your previous code was:

.Application.ExecuteExcel4Macro("Show.ToolBar(""Ribbon"", boolStatus)")

The argument passed to ExecuteExcel4Macro would look like Show.ToolBar("Ribbon", boolStatus).

Using first code snippet above, it would look like:

Show.ToolBar("Ribbon", False)

edited Aug 15, 2013 at 3:22

answered Aug 15, 2013 at 2:59

Tim

28.6k8 gold badges65 silver badges80 bronze badges

1 Comment

Jose M. Over a year ago

Thanks for the explanation. I could not figure out the issue. I tried your solution and it worked perfect.

Collectives™ on Stack Overflow

Setting Function Boolean var to False or True

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related