Consulting

Results 1 to 3 of 3

Thread: Creating button by offsetting space

  1. #1
    VBAX Regular
    Joined
    Oct 2015
    Posts
    11
    Location

    Creating button by offsetting space

    Hi

    I'm using this to create a button with an assigned macro

    Sub CreateButton()
    ActiveSheet.Buttons.Add(199.5, 20, 81, 36).Select
    Selection.Name = "New Button"
    Selection.OnAction = "MoveValue"
    ActiveSheet.Shapes("New Button").Select
    Selection.Characters.Text = "Check Totals"
    End Sub

    However, I want to create the button multiple times, each time offsetting from the previous button running down the Y axis. Essentially I want something like End(xlUp).Offset(1), something that recognises that the space is already taken by another button.

    Many Thanks

  2. #2
    VBAX Regular
    Joined
    Nov 2011
    Location
    Ufa
    Posts
    72
    Location
    maybe something like this
    Sub CreateButton()
    Dim i&
    With ActiveSheet
        i = .Shapes.Count
        With .Buttons.Add(199.5, 20 + 46 * i, 81, 36)
            .Name = "New Button" & Format(i, "00")
            .OnAction = "MoveValue"
            .Characters.Text = "Check Totals " & Format(i, "00")
        End With
    End With
    End Sub

  3. #3
    VBAX Regular
    Joined
    Oct 2015
    Posts
    11
    Location
    I'm going to have multiple buttons on the page already so that doesn't quite solve the problem. Different buttons are going to be generated in different areas of the sheet, so this will offset these buttons by too much

Posting Permissions

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