List Excel command bars and controls

Recently I had to update some old Visual Basic code to talk to a new third-party Excel toolbar (Visual Basic refers to toolbars as CommandBars internally). To do this I needed to know the names of the toolbars and toolbar buttons. I found a couple of pieces of code online that came close but didn’t quite get me what I needed. So I threw this together.

Sub ListCommandBarsAndControls()

    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Create a header on the first row of the spreadsheet
    ws.Rows(1).Cells(1) = "BAR NAME"
    ws.Rows(1).Cells(2) = "BAR VISIBLE"
    ws.Rows(1).Cells(3) = "BAR BUILTIN"
    ws.Rows(1).Cells(4) = "CONTROL ID"
    ws.Rows(1).Cells(5) = "CONTROL CAPTION"
    ws.Rows(1).Cells(6) = "CONTROL ENABLED"
    
    ' Set a variable so the following starts writing on the second row
    targetRow = 2
    
    ' Iterate through all command bars
    For i = 1 To Application.CommandBars.Count

        Set sCmdBar = Application.CommandBars(i)
        ws.Rows(targetRow).Cells(1) = sCmdBar.Name
        ws.Rows(targetRow).Cells(2) = sCmdBar.Visible
        ws.Rows(targetRow).Cells(3) = sCmdBar.BuiltIn
        
        ' And for each command bar, iterate through all the available controls
        For j = 1 To Application.CommandBars(i).Controls.Count
            Set sControl = Application.CommandBars(i).Controls(j)
            ws.Rows(targetRow).Cells(1) = sCmdBar.Name
            ws.Rows(targetRow).Cells(2) = sCmdBar.Visible
            ws.Rows(targetRow).Cells(3) = sCmdBar.BuiltIn
            ws.Rows(targetRow).Cells(4) = sControl.ID
            ws.Rows(targetRow).Cells(5) = sControl.Caption
            ws.Rows(targetRow).Cells(6) = sControl.Enabled
            targetRow = targetRow + 1
        Next j

    Next i

    ' Some times it takes a while to complete so pop up 
    ' a message box to make it clear when it's finished
    MsgBox "Complete!"

End Sub

If you create a new macro, paste this code in and run it you should get output that looks something like the following screenshot. Note that I filtered the output here after running the script to only show me information about toolbars that aren’t built into Excel, i.e. third-party toolbars that are generated by Excel plugins.

VBA - find out variable position in array.

 

Use Application.Match

Example: 

Dim pos, arr, val

arr=Array(1,2,4,5)
val = 4

pos=Application.Match(val, arr, False)

if not iserror(pos) then
   Msgbox val & " is at position " & pos
else
   Msgbox val & " not found!"
end if

Revision cloud line tool missing in my version of Acrobat XI Pro

Acrobat XI Pro 11.0.23

Comments tool cloud line and polygon icon dim in tools bar 

It is possible that you are running your copy of Acrobat in Touch Mode - 

you can turn this off by going to Preferences>General>Touch Mode

Export and Import Microsoft DHCP Allow and Deny list

 

Get-DhcpServerv4Filter

Module:

dhcpserver

Gets MAC addresses from the allow list or the deny list on the DHCP server service.

Syntax

PowerShell

Get-DhcpServerv4Filter
   [[-List] <String>]
   [-ComputerName <String>]
   [-CimSession <CimSession[]>]
   [-ThrottleLimit <Int32>]
   [-AsJob]
   [<CommonParameters>]

Description

The Get-DhcpServerv4Filter cmdlet gets the list of all MAC addresses from the allow list or the deny list on the Dynamic Host Configuration Protocol (DHCP) server service.

If the List parameter is not specified, both allow and deny filters are returned.

Examples

Example 1: Get all MAC address for a computer

PowerShell

PS C:\> Get-DhcpServerv4Filter -ComputerName "dhcpserver.contoso.com"

This example gets all of the MAC addresses in the allowed and denied lists configured on the DHCP server service on the computer named dhcpserver.contoso.com.

Parameters

-AsJob

Runs the cmdlet as a background job. Use this parameter to run commands that take a long time to complete. The cmdlet immediately returns an object that represents the job and then displays the command prompt. You can continue to work in the session while the job completes. To manage the job, use the *-Job cmdlets. To get the job results, use the Receive-Job cmdlet. For more information about Windows PowerShell® background jobs, see about_Jobs.

TABLE 1

Type:	SwitchParameter
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-CimSession

Runs the cmdlet in a remote session or on a remote computer. Enter a computer name or a session object, such as the output of a New-CimSession or Get-CimSession cmdlet. The default is the current session on the local computer.

TABLE 2

Type:	CimSession[]
Aliases:	Session
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-ComputerName

Specifies the DNS name, or IPv4 or IPv6 address, of the target computer that runs the DHCP server service.

TABLE 3

Type:	String
Aliases:	Cn
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-List

Specifies the list from which one or more MAC addresses are to be retrieved. The acceptable values for this parameter are: Allow or Deny.

TABLE 4

Type:	String
Accepted values:	Allow, Deny
Position:	1
Default value:	None
Accept pipeline input:	True (ByPropertyName)
Accept wildcard characters:	False

-ThrottleLimit

Specifies the maximum number of concurrent operations that can be established to run the cmdlet. If this parameter is omitted or a value of 0 is entered, then Windows PowerShell® calculates an optimum throttle limit for the cmdlet based on the number of CIM cmdlets that are running on the computer. The throttle limit applies only to the current cmdlet, not to the session or to the computer.

TABLE 5

Type:	Int32
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

Inputs

None

Outputs

Microsoft.Management.Infrastructure.CimInstance#root/Microsoft/Windows/DHCP/DhcpServerv4Filter[]

The Microsoft.Management.Infrastructure.CimInstance object is a wrapper class that displays Windows Management Instrumentation (WMI) objects. The path after the pound sign (#) provides the namespace and class name for the underlying WMI object.

Related Links

• Add-DhcpServerv4Filter
• Get-DhcpServerv4FilterList
• Remove-DhcpServerv4Filter
• Set-DhcpServerv4FilterList

Add-DhcpServerv4Filter

Module:

dhcpserver

Adds a MAC address filter to the DHCP server service.

Syntax

PowerShell

Add-DhcpServerv4Filter
   [-ComputerName <String>]
   [-Description <String>]
   [-MacAddress] <String[]>
   [-List] <String>
   [-Force]
   [-PassThru]
   [-CimSession <CimSession[]>]
   [-ThrottleLimit <Int32>]
   [-AsJob]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Description

The Add-DhcpServerv4Filter cmdlet adds the specified MAC address filter to the Dynamic Host Configuration Protocol (DHCP) server service. The MAC address can be added to the allow list or the deny list.

Examples

Example 1: Add a client to the allowed filter

PowerShell

PS C:\> Add-DhcpServerv4Filter -List Allow -MacAddress "F0-DE-F1-7A-00-5E" -Description "Laptop 09"

This example adds the specified client identified by the MAC address to the allowed list of MAC address filters.

Example 2: Add multiple clients to the allowed filter

PowerShell

PS C:\> Add-DhcpServerv4Filter -List Allow -MacAddress "F0-DE-F1-7A-00-5E", "F0-DE-F1-7A-00-5C"

This example adds the specified clients identified by their MAC address to the allowed list of MAC address filters.

Example 3: Add address filters from a file

PowerShell

PS C:\> Import-Csv -Path "MacAddressFilters.csv" | Add-DhcpServerv4Filter -ComputerName "dhcpserver.contoso.com" -List Allow

This example adds all of the MAC address filters in the file that is named MacAddressFilters.csv to the allow MAC address list of the DHCP server service running on the computer named dhcpserver.contoso.com. The Import-Csv cmdlet returns the objects that have Mac address filter fields that are piped to this cmdlet, which in turn adds the MAC address filters to the server. The file that is named MacAddressFilters.csv should be in the following comma-separated values (CSV) format:

MacAddress,Description

1a-1b-1c-1d-1e-1f,Computer1

2a-2b-2c-2d-2e-2f,Computer2

3a-3b-3c-3d-3e-3f,Computer3

Parameters

-AsJob

Runs the cmdlet as a background job. Use this parameter to run commands that take a long time to complete. The cmdlet immediately returns an object that represents the job and then displays the command prompt. You can continue to work in the session while the job completes. To manage the job, use the *-Job cmdlets. To get the job results, use the Receive-Job cmdlet. For more information about Windows PowerShell® background jobs, see about_Jobs.

TABLE 1

Type:	SwitchParameter
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-CimSession

Runs the cmdlet in a remote session or on a remote computer. Enter a computer name or a session object, such as the output of a New-CimSession or Get-CimSession cmdlet. The default is the current session on the local computer.

TABLE 2

Type:	CimSession[]
Aliases:	Session
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-ComputerName

Specifies the DNS name, or IPv4 or IPv6 address, of the target computer that runs the DHCP server service.

TABLE 3

Type:	String
Aliases:	Cn
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-Confirm

Prompts you for confirmation before running the cmdlet.

TABLE 4

Type:	SwitchParameter
Aliases:	cf
Position:	Named
Default value:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Description

Specifies the description string for the MAC address filter being added.

TABLE 5

Type:	String
Position:	Named
Default value:	None
Accept pipeline input:	True (ByPropertyName)
Accept wildcard characters:	False

-Force

Specifies that, if one or more of the MAC addresses are already present in the allow or deny list, the matching MAC addresses are deleted and the new entries created.

This parameter is useful in the case where the MAC address specified is already present in one list, such as the allow list, and the same MAC address now has to be added to the other list, such as the deny list.

If this parameter is not specified, the cmdlet fails if the specified MAC address is already present in any of the lists.

TABLE 6

Type:	SwitchParameter
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-List

Specifies the list to which one or more MAC addresses are to be added. The acceptable values for this parameter are: Allow or Deny.

TABLE 7

Type:	String
Accepted values:	Allow, Deny
Position:	1
Default value:	None
Accept pipeline input:	True (ByPropertyName)
Accept wildcard characters:	False

-MacAddress

Specifies one or more MAC addresses which are to be added to the MAC address filter list.

TABLE 8

Type:	String[]
Aliases:	ClientId
Position:	2
Default value:	None
Accept pipeline input:	True (ByPropertyName)
Accept wildcard characters:	False

-PassThru

Returns an object representing the item with which you are working. By default, this cmdlet does not generate any output.

TABLE 9

Type:	SwitchParameter
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-ThrottleLimit

Specifies the maximum number of concurrent operations that can be established to run the cmdlet. If this parameter is omitted or a value of 0 is entered, then Windows PowerShell® calculates an optimum throttle limit for the cmdlet based on the number of CIM cmdlets that are running on the computer. The throttle limit applies only to the current cmdlet, not to the session or to the computer.

TABLE 10

Type:	Int32
Position:	Named
Default value:	None
Accept pipeline input:	False
Accept wildcard characters:	False

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

TABLE 11

Type:	SwitchParameter
Aliases:	wi
Position:	Named
Default value:	False
Accept pipeline input:	False
Accept wildcard characters:	False

Inputs

Microsoft.Management.Infrastructure.CimInstance#root/Microsoft/Windows/DHCP/DhcpServerv4Filter

The Microsoft.Management.Infrastructure.CimInstance object is a wrapper class that displays Windows Management Instrumentation (WMI) objects. The path after the pound sign (#) provides the namespace and class name for the underlying WMI object.

Outputs

Microsoft.Management.Infrastructure.CimInstance#root/Microsoft/Windows/DHCP/DhcpServerv4Filter[]

The Microsoft.Management.Infrastructure.CimInstance object is a wrapper class that displays Windows Management Instrumentation (WMI) objects. The path after the pound sign (#) provides the namespace and class name for the underlying WMI object.

Related Links

• Get-DhcpServerv4Filter
• Get-DhcpServerv4FilterList
• Get-DhcpServerv4Lease
• Remove-DhcpServerv4Filter
• Set-DhcpServerv4FilterList

Get-DhcpServerv4Filter -List Allow -ComputerName <primary DHCP host> | Add-DhcpServerv4Filter -Force

SyncDHCPServerFilters.ps1
# SyncDHCPServerFilters.ps1
# Author: Nathan Thomas
# Date: 10/23/2018
#
# Using Task Scheduler, trigger the update of the remote failover DHCP Server list
# upon the following event ID filter changes.
# Event ID 123 - Added to the IPv4 Allow List
# Event ID 124 - Removed from the IPv4 Allow List
# Event ID 127 - Added to the IPv4 Deny List
# Event ID 128 - Removed from the IPv4 Deny List
#
# NOTE: If you want to be able to edit the filter list on both DHCP servers and
# still have them sync, you would add a scheduled task on both servers, each
# pointing to the other server to update.

$RemoteDHCPFailoverServer = "remotedhcpserver.yourdomain.com";

# Get the REMOTE filters from $RemoteServer
$rfilters = invoke-command -computername $RemoteDHCPFailoverServer { Get-DhcpServerv4Filter };

# Delete the REMOTE Filter Set
If ($rfilters.count -ne "0") {
	Invoke-Command -ComputerName $RemoteDHCPFailoverServer -ScriptBlock {
		ForEach ($filter in $using:rfilters) {
			Remove-DhcpServerv4Filter -MacAddress $filter.MacAddress;
		}
	}
}

# Get the LOCAL filters from localhost
$lfilters = Get-DhcpServerv4Filter;

# Import the new Filter Set on $RemoteServer
If ($lfilters.count -ne "0") {
	Invoke-Command -ComputerName $RemoteDHCPFailoverServer -ScriptBlock {
		ForEach ($filter in $using:lfilters) {
			Add-DhcpServerv4Filter -List $filter.List -MacAddress $filter.MacAddress -Description $filter.Description;
		}
	}
}
Task Scheduler
General
Name: Sync DHCP Server Filter List
Security options:
When running the task, use the following user account: Domain\Domain Administrator Account
Run whether user is logged on or not - Radio button
Run with highest privileges - checked
Configure for: Windows Server 2012 R2

Triggers
Begin the task: On an event
Basic - Radio button
Log: Microsoft-Windows-DHCP Server Events/Opertational
Source: DHCP-Server
Event ID: 123
Enabled - checked

Begin the task: On an event
Basic - Radio button
Log: Microsoft-Windows-DHCP Server Events/Opertational
Source: DHCP-Server
Event ID: 124
Enabled - checked

Begin the task: On an event
Basic - Radio button
Log: Microsoft-Windows-DHCP Server Events/Opertational
Source: DHCP-Server
Event ID: 127
Enabled - checked

Begin the task: On an event
Basic - Radio button
Log: Microsoft-Windows-DHCP Server Events/Opertational
Source: DHCP-Server
Event ID: 128
Enabled - checked

Actions
Action: Start a program
Settings:
Program/Script: PowerShell
Add arguments (optional): .\SyncDHCPServerFilters.ps1 (name of PowerShell script)
Start in (optional): C:\ (path to folder where script resides)

Conditions
Leave at default

Settings
Allow task to be run on demand - checked
Stop the task if it runs longer than: 1 hour - checked
If the running task does not end when requested, force it to stop - checked
If the task is already running, then the following rule applies: Do not start a new instance.