Posts by Monkeyface

Re: Saving a toolbar button to an add in

Nevermind. Found out it was Style = .msoButtonIconandCaption

I have an addin that is working very well thanks to many of you on this board. However, I have a toolbar that has a custom button image. Is there a way to find the faceid for this button image? In other words, is it even possible to save my custom button image in an addin?

Also, When the toolbar is created, I want the faceid AND the text to show up. How do I set that?

Re: Intalling Excel Add-in that contains a button to run macro

Yes, I added the CashMan form to the add-in. Would you like me to attach the add-in?

I just deleted the userform that was in there and re-added it and now it works. I don't know why, but I guess all is good right now. Thank you for the help.

Re: Intalling Excel Add-in that contains a button to run macro

As a refresher, here is the code that I posted. Most of it is the same as yours. I tried CashMan.Load and that gave me an error that said "Compile error: Method or data member not found"

Option Explicit
 
Sub DeleteMenu()
    On Error Resume Next
    CommandBars(1).Controls("MyMacros").Delete
End Sub
 
Sub CreateMenu() 'WillR June 2005 Create Menu for file
    Dim HelpMenu As CommandBarControl
    Dim NewMenu As CommandBarPopup
    Dim MenuItem As CommandBarControl
    Dim Submenuitem As CommandBarButton
     
     '   Delete the menu if it already exists
    Call DeleteMenu
     
     '   Find the Help Menu
    Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
     
    If HelpMenu Is Nothing Then
         '       Add the menu to the end
        Set NewMenu = CommandBars(1).Controls.Add _
        (Type:=msoControlPopup, _
        temporary:=True)
    Else
         '      Add the menu before Help
        Set NewMenu = CommandBars(1).Controls.Add _
        (Type:=msoControlPopup, _
        Before:=HelpMenu.Index, _
        temporary:=True)
    End If
     
     '   Add a caption for the menu
    NewMenu.Caption = "&MyMacros"
     
     '   ADD NEW MENU ITEM
    Set MenuItem = NewMenu.Controls.Add _
    (Type:=msoControlButton)
    With MenuItem
        .Caption = "&Cash Management"
        .FaceId = 183
        .OnAction = "CashMgmt"
    End With
     '   ADD NEW MENU ITEM
    Set MenuItem = NewMenu.Controls.Add _
    (Type:=msoControlButton)
    With MenuItem
        .Caption = "&Another Macro..."
        .FaceId = 124
        .OnAction = "Message2"
    End With
End Sub
 
Sub CashMgmt()
    CashMan.Load
End Sub
 
Sub Message2()
    MsgBox "This is message 2"
     
End Sub

Re: Intalling Excel Add-in that contains a button to run macro

I used the above code, however I tweaked it so that Message1() looks like this:

Cashman.Show

I previously added a form called Cashman, which I wanted to load. For some reason it keeps giving me an error "Invalid procedure call or argument". Can someone please help me figure out how to load the form?

Thank you.

I have a userform with a dropdown and submit button that works fine right now. However, the dropdown is populated from a spreadsheet. I have coded it so that if the path to the spreadsheet cannot be found, the code exit out.

When I run the code and it cannot find the path, it is supposed to go to the error code and then exit out. Instead, it pops up the msgbox as it is supposed to and then loads the form anyway, but just with a blank dropdown list. How do I get it to just exit out with popping up the form?

Here is the code:

'Function to check whether workbook is open
Function BookOpen(Bk As String) As Boolean
    Dim T As Excel.Workbook
    Err.Clear 'clears away any errors
    On Error Resume Next 'If the code runs into an error, it skips it and continues
    Set T = Application.Workbooks(Bk)
    BookOpen = Not T Is Nothing
    'If the workbook is open, then T will hold the workbook object and therefore
    'will NOT be Nothing
    Err.Clear
    On Error GoTo 0
End Function


Private Sub UserForm_Initialize()
    
'Open List file
    Dim strFile As String, strPath As String, FinalRow As Integer
    strPath = "C:\Path\"
    strFile = "List.xls"
    
    IsOpen = BookOpen(strFile)
    If IsOpen Then
        Windows(strFile).Activate
    Else
        On Error GoTo InvDoesNotExist
        Workbooks.Open strPath & strFile
        Windows(strFile).Activate
        On Error GoTo 0
    End If
    Sheets(1).Select
    
'Populate form
    Dim arrName
    arrName = Sheets(1).Range("A2", Range("a65536").End(xlUp)).Value
     
    Me.ComboBox1.List = arrName
    Me.ComboBox1.Style = fmStyleDropDownList
    Me.ComboBox1.BoundColumn = 0
    Me.ComboBox1.ListIndex = 0
    Exit Sub


InvDoesNotExist:
    MsgBox "The file " & strPath & strFile & " does not exist."
    UserForm1.Hide
    Exit Sub
    
End Sub

Thank you for your help.

Re: Passing a variable into a function

Thank you both for your help. That is just what I needed. I'm still a noob, and I'm learning it as I go.

Re: Using a combobox within a userform

That worked great! Thank you.

Re: Passing a variable into a function

So how do I pass the strFunctionDate value to the function? Or do you mean it isn't possible to pass anything to the function?

I am trying to pass a variable into a function. Every time I attempt to do so, the variable within the function is blank. Let me show you my code:

In this part of the code, I am pulling 6 digits that are dates, but just not formatted as dates (example 060105 for June 1, 2005, 123104 for Dec 31, 2004, etc...) and putting them into the variable strFunctionDate.

Sub TransformDate()
strFunctionDate = Right(ActiveCell.Worksheet.Name, 6)
Call FormatDate
End Sub

This is the function. strFunctionDate is always Empty when it gets to this point, whereas I would like it to retain the value as noted above.

Function FormatDate()
    If Len(strFunctionDate) = 6 Then
        strFunctionDate = Left(strFunctionDate, 2) & "/" & Mid(strFunctionDate, 3, 2) & "/" & Right(strFunctionDate, 2)
    ElseIf Len(strFunctionDate) = 7 Then
        strFunctionDate = Left(strFunctionDate, 1) & "/" & Mid(strFunctionDate, 2, 2) & "/" & Right(strFunctionDate, 4)
    ElseIf Len(strPaymentDate) = 8 Then
        strFunctionDate = Left(strFunctionDate, 2) & "/" & Mid(strFunctionDate, 3, 2) & "/" & Right(strFunctionDate, 4)
    End If
End Function

Please help.

Thank you.

Re: Using a combobox within a userform

Very interesting. I took your spreadsheet and played around with it a little and discovered that it works flawlessly until I introduce this section:

Private Sub Combobox1_Change() 
    MsgBox Me.ComboBox1.ListIndex 
End Sub

At that point, it pops up the -1 twice and then the zero. I wonder why. In the meantime, at least the main code works.

Thank you for your help.

Re: Using a combobox within a userform

That definitely made my code look cleaner. However, it is still producing the same problem.

The msgbox still pops up a -1 twice, a 0, and then it pops up the dropdown list.

Can anyone please help me figure out why?

Re: Using a combobox within a userform

Thank you.

I have decided to use a spreadsheet to house the information. My current spreadsheet has the current values:

CRM | CRM Name | Location
JS | John Smith | Irvine
NJ | Nancy Jones | Irvine
SH | Shannon Hughes | Irvine
NR | Nathan Robinson | Irvine
BR | Brett Roberts | Irvine

What I want to do is create a dropdown that shows the CRM Name (column 2). When I run the code, it doesn't work quite right.

Here is my code:

Private Sub UserForm_Initialize()
    Dim FinalRow As Integer, arrCRMName
    FinalRow = Range("A65536").End(xlUp).Row
    ReDim arrCRMName(1 To FinalRow, 1 To 5)
    arrCRMName = Range("A1:C" & FinalRow)
    
    For i = 2 To UBound(arrCRMName)
        Me.ComboBox1.AddItem arrCRMName(i, 2)
    Next i
    Me.ComboBox1.Style = fmStyleDropDownList
    Me.ComboBox1.BoundColumn = 0
    Me.ComboBox1.ListIndex = 0
End Sub

In order to test to see if it was working correctly, I created the following code to have it return the list index value

Private Sub Combobox1_Change()
        Call ViewForm
End Sub


Function ViewForm()
    MsgBox Me.ComboBox1.ListIndex
End Function

When I run this code, it pops up the messagebox with a -1 twice, then a 0, and then it opens the dropdown box. Can someone help me figure out why it is doing this?

Thank you

I have created a complex macro. What I want to do now is create a simple form that will allow me to select a person from a dropdown list. Once this person is selected, and the submit button is clicked, the macro will run using the value associated with that person. The macro is already in place. I just need to create the form with the dropdown box to select that person. I don't want to pull the information from a spreadsheet. Instead, I just want to hardcode the names into the code.

I know how to create a userform. I just don't know how to code the dropdown (combobox) and the submit and cancel buttons. Please show me the best way to quickly code this.

Thank you.

Re: Using combobox within a userform

No problem. Here is what I want to do.

I have created a complex macro. What I want to do now is create a simple form that will allow me to select a person from a dropdown list. Once this person is selected, and the submit button is clicked, the macro is run for that particular person with specifics that relate to that person (this part is already in place). I just need to create the form with the dropdown box to select that person. I don't want to pull the information from a spreadsheet. Instead, I just want to hardcode the names into the code.

Let me know if you need more information.

thank you.

Re: Creating a userform with combobox

The problem for me isn't necessarily creating the userform. The problem is creating a combobox that works as well as a submit button and cancel button that work. I've already created the userform but it is nothing but a shell until I put some code behind it.

Does that make sense?

Hello. I am a total noob when it comes to userforms. I want to create a userform with a simple combobox that contains 4 different selections. Along with this combobox will be a Submit button and a Cancel button.

Can someone please help me with the code to do this?

Thank you.

Re: Adding toolbars to add-ins

Great! That should help. Thanks.

I've read a lot about how to create an add-in. However, I still can't figure out how to attach my toolbar to an add-in.

I've created a macro in my Personal.xls file and I've also created a custom toolbar called MyMacros. I want to distribute this macro including the toolbar to other people who are very untechnical and I want to make installing the macro on their computers as seamless as possible.

Could someone please give me precise instructions on how to create the add-in with the toolbar? I have tried to create add-ins but they don't seem to work quite right and don't include the toolbar.

Thank you in advance.

Re: Collecting distinct data from a large table

Got it! And I got it to work. Thank you for your help!