0

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
dorichidori
  • 5
  • 1
  • 4

1 Answers1

1

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
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • the modules are not tests and I can not get them running without calling them this way. Regarding the command Button, my issue is that it is not showing up. I tried changing a few things like in your approach, but it didn't change anything – dorichidori May 03 '18 at 17:22
  • You realise that you have to either programmatically add the button or add it via the developer tab before clicking? – QHarr May 03 '18 at 17:29
  • maybe I didn't phrase my question correctly. My question is through VBA-Excel, how do i show my button and customize the size etc. I apologies for the confusion – dorichidori May 03 '18 at 17:32
  • Ok. So are you adding a button to the worksheet or a form? – QHarr May 03 '18 at 17:41
  • This link runs through adding different types of buttons in different places https://stackoverflow.com/questions/50144020/overview-of-differences-between-form-controls-and-activex-controls-in-excel – QHarr May 03 '18 at 17:43
  • Is it an activex or form control button you are adding? – QHarr May 03 '18 at 17:48
  • It's an activex button, I will give that a try, thank you – dorichidori May 03 '18 at 17:57
  • Ok. The above edit shows you how to add an activex through code and position. I guess what I don't understand is why you don't go to the developer tab and > controls > add activex command button and place it where you want. Click the design mode button on developer tab so you can resize the button manually. Or have I missed something? Apologies if I have. – QHarr May 03 '18 at 17:59
  • Because I am working with a large amount of data and I have some settings turned off. If I were to add the button through GUI then it will slow down my upload at a certain point. If it comes down to it, I will try it that way. Thank you for your help! – dorichidori May 03 '18 at 18:04
  • Ok. Well the above and the link given should help. – QHarr May 03 '18 at 18:08