CODEWITHSUNDEEP
vbaexcel
dorichidori
dorichidori

Reputation: 5

VBA-Excel Button not showing, running several modules

I am trying to create a button that run filters through my data. I would like to customize the size, name (through vba) but I can not get my button to show up. Not too sure what is missing.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub
Public Sub CommandButton1_Click()
    Call Module1.Main
End Sub

Upvotes: 0

Views: 491

Answers (1)

QHarr
QHarr

Reputation: 84465

Not sure what the problem is. If in the same workbook and you want to call a sub from different locations declare its scope Public.

For example if I have the following in sheet1 code pane:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
Public Sub CommandButton1_Click()
   Test1
   Test2
   Test3
End Sub

Public Sub Test1()    
    MsgBox "Test1"    
End Sub

And in module 1 

Public Sub Test2()   
    MsgBox "Test2"    
End Sub

And in module 2

Public Sub Test3()    
    MsgBox "Test3"   
End Sub

Everything runs fine from the CommandButton1_Click due to the Public scope declarations.

Notes:

  1. Call is obsolete and not needed here
  2. Prefix of the module name shouldn't be needed either I think
  3. The CommandButton1_Click would go in the code pane of the sheet containing the button.

Edit:

If you are after adding a button and positioning it I would refer you to this question which lists the different methods. If you find that useful please remember to upvote the author.

An example from that, regarding adding an ActiveX and positioning is as follows:

Sub activexControl_add()
    'create ActiveX control
    Dim ws As Worksheet: Set ws = ActiveSheet
    With ws.OLEObjects.Add("Forms.CommandButton.1")
        .Left = 25
        .Top = 25
        .Width = 75
        .Height = 75
        .Name = "xCommandButton1" 'name control immediately (so we can find it later)
    End With
End Sub

From my own code samples I have an example where, due to users having different screen resolutions, my ActiveX objects were moving, so I put the following in the workbook open event to ensure the buttons were where I wanted them and sized as I wanted them:

 With wsCCGOverview '<== sheet containing combobox control (this could be your button)

          .OLEObjects("ComboBox1").Left = 296.25
          .OLEObjects("ComboBox1").Width = 132.75
          .OLEObjects("ComboBox1").Height = 40
          .OLEObjects("ComboBox1").Top = 187.5
          .Shapes("ComboBox1").ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft

End With

Upvotes: 1

Related Questions