none
Automatically Update Shape Size Using Inputs from Scroll Bars? RRS feed

  • Question

  • Please bear with me as I'm new to VBA. I'm using Excel 2013 and have a spreadsheet where I am using two scrollbars to control length and width of a rectangle.  In this example, I'm having the scroll bars report their positions in cells H13 and H14, then using the value in those cells to generate the rectangle.  It works great if I click anywhere in the sheet after moving the scroll bars.  But what I really want is to have the rectangle automatically change its shape whenever either scrollbar is moved.  Is that possible? 

    My code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim shp As shape
        For Each shp In ActiveWorkbook.ActiveSheet.Shapes
          If shp.AutoShapeType = msoShapeRectangle Then shp.Delete
        Next shp

    Set myDocument = Worksheets(1)
    Dim Length As Single, Width As Single
    Width = Range("h13").Value
    Length = Range("h14").Value
    myDocument.Shapes.AddShape msoShapeRectangle, 200, 100, Length, Width
       
    End Sub

    Thursday, August 11, 2016 3:07 PM

Answers

  • Re:  control shape size with scrollbars

    You will need to use scrollbars from the Control tool box not the Forms toolbar.
    You have the choice when inserting from the Ribbon.

    Then in the worksheet module for the worksheet with the scrollbars add this code...
    '---
    Private Sub ScrollBar1_Change()
      Me.Shapes("Rectangle 1").Width = Me.ScrollBar1.Value
    End Sub

    Private Sub ScrollBar2_Change()
      Me.Shapes("Rectangle 1").Height = Me.ScrollBar2.Value
    End Sub
    '---
    You will have to adjust the shape name and the scrollbar names above to reflect actual names.
    Also, in the properties window for the sheet, you will need to set values for the scroll bars.
       (you should be in design mode and have selected a scroll bar)
       At least establish Min and Max values and a Value value.  I used 5, 200 and 100 for my test.
    Note:  "Me" is a reference to the worksheet.

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0


    Friday, August 12, 2016 2:36 AM

All replies

  • Re:  control shape size with scrollbars

    You will need to use scrollbars from the Control tool box not the Forms toolbar.
    You have the choice when inserting from the Ribbon.

    Then in the worksheet module for the worksheet with the scrollbars add this code...
    '---
    Private Sub ScrollBar1_Change()
      Me.Shapes("Rectangle 1").Width = Me.ScrollBar1.Value
    End Sub

    Private Sub ScrollBar2_Change()
      Me.Shapes("Rectangle 1").Height = Me.ScrollBar2.Value
    End Sub
    '---
    You will have to adjust the shape name and the scrollbar names above to reflect actual names.
    Also, in the properties window for the sheet, you will need to set values for the scroll bars.
       (you should be in design mode and have selected a scroll bar)
       At least establish Min and Max values and a Value value.  I used 5, 200 and 100 for my test.
    Note:  "Me" is a reference to the worksheet.

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0


    Friday, August 12, 2016 2:36 AM
  • Thanks Jim; I appreciate the help. 

    <Old n00b.>

    Tuesday, August 16, 2016 2:42 PM