+ Reply to Thread
Results 1 to 3 of 3

Looping through controls on a worksheet to set control icons

Hybrid View

  1. #1
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Looping through controls on a worksheet to set control icons

    I am trying to loop through controls on a worksheet to set the mouse icon. It works fine on a form like this:
    Sub SetFormControlCursors(form_name As UserForm, cursor_name As String)
        Dim z As Control
        For Each z In form_name.Controls
          If TypeName(z) = "CommandButton" Then
            z.MousePointer = fmMousePointerCustom
            z.MouseIcon = LoadPicture(cursor_name)
          End If
        Next z
    End Sub
    but adapted for a worksheet, nothing happens:
    Sub SetSheetCursors(sheetName As String, cursor_name As String)
        Dim z As Shape
        Dim s As String
        Dim w1 As Worksheet: Set w1 = Sheets(sheetName)
        For Each z In Sheets(sheetName).Shapes
          If TypeName(z) = "CommandButton" Then
            z.MousePointer = fmMousePointerCustom
            z.MouseIcon = LoadPicture(cursor_name)
          End If
        Next z
    End Sub
    Any ideas why? The controls are Active X command buttons. Thanks!

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Looping through controls on a worksheet to set control icons

    It didn't work because they are Active X command buttons. The shape property is for Form controls. Note: The TypeName is not going to work as you had it. I put in a Debug.Print line so you can see what the type actually comes up as.

    Sub SetSheetCursors(sheetName As String, cursor_name As String)
        Dim o As Object
        Dim s As String
        Dim w1 As Worksheet: Set w1 = Sheets(sheetName)
        For Each o In w1.OLEObjects
            Debug.Print TypeName(o) 'reports as OLEObject
            Debug.Print o.Name 'gives name of command button or other object in sheet
            If Not InStr(1, o.Name, "CommandButton") = 0 Then 'assuming all the commandbuttons are called CommandButtonX where X is a number
                'o.MousePointer = fmMousePointerCustom 'didn't test this portion out.  Not sure if it works
                'o.MouseIcon = LoadPicture(cursor_name) 'didn't test this portion out.  Not sure if it works
          End If
        Next o
    End Sub

  3. #3
    Registered User
    Join Date
    08-30-2014
    Location
    Lincoln, England
    MS-Off Ver
    Office 365
    Posts
    74

    Re: Looping through controls on a worksheet to set control icons

    Thanks for the reply and the help, much appreciated. The two untested lines didn't work - they came up with "object doesn't support this property or method'. Also, the controls are not numbered - I've given them names, but they still have "CommandButton" in the name. I'll keep at it for a while...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] adapt userform loop using control name containing variable for worksheet activex controls
    By bmeister in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-22-2014, 02:45 PM
  2. Enable/Disable Form controls on a worksheet (not Active X Control)
    By vaniravi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-29-2012, 07:42 AM
  3. Icons in TreeView Control
    By jfmorales in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2011, 02:37 PM
  4. looping through controls
    By cmpcwil2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-18-2006, 05:48 AM
  5. [SOLVED] Looping through controls on a MultiPage control
    By Paul Martin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-26-2005, 01:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1