Is there an API for VBA like there is for Java?

UWMmakow

New Member
Joined
Feb 25, 2014
Messages
30
My only programming experience is with Java, and with basic knowledge of coding and logic from that, I'm teaching myself VBA. My biggest, biggest, biggest frustration is that I have no clue where to look to learn the syntax and language! I can imagine the flow of code perfectly in my mind, but I just don't know how to write it out.

Ideally there would be some sort of document like this but I'm beginning to believe that doesn't exist for VBA. I've found this page but it doesn't seem to be complete.

For instance, right now I'm trying to figure out how to link a range to a listbox to populate it from, and I've been Googling around and trying every snippet of code I find to no avail. If there was just a page somewhere that listed all the properties and methods you can call on the listbox class, I'd be golden. But that just doesn't seem to be out there.

Am I just using the reference guide wrong? Are the answers I'm looking for hidden in that page? If not... where in the hell do people go to learn the language??

You can imagine how frustrated I'm getting. I really appreciate any direction on this.
 
I could be wrong but I don't think you can set a Forms Listbox range in code. You can set an ActiveX Listbox in code. For a forms listbox you would want to use a strategy like a cascading combobox - set a range in the listbox that is a named range, with its reference containing an Indirect() formula that points to the range you want to use. The range you want to use would depend on the option chosen, and this triggers either one named range or the other named range (months or weeks). I'll see if I can cook up an example when I'm home tonight. No code at all! Conversely, use an ActiveX listbox and you can use code - as well as catch events such as the option button being clicked.

ξ

Note: by Forms controls, in an Excel context, I mean what you see when you go to the developer tab and click the Insert button. There you have the choice of "Forms" controls, and "ActiveX" controls. Forms controls here mean (really) "Excel Forms Controls". The were developed specifically for use in and with Excel. But these forms controls are *not* the type of forms that show up in the links we have been posting in earlier posts about Listbox objects.
 
Last edited:
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You can certainly set a Forms control's properties in code. It's easier using the Activesheet.Listboxes("List Box 1") syntax to access them.
 
Upvote 0
Good to know. Question: can we intercept the option button change if we are using Forms option buttons? Or do we need to look for a worksheet change event or worksheet calculate event?

ξ
 
Upvote 0
Good to know. Question: can we intercept the option button change if we are using Forms option buttons? Or do we need to look for a worksheet change event or worksheet calculate event?

ξ
The only way I could find was the "Assign Macro..." feature. It only lets you deal with one event (click for buttons, change for listboxes), but off the top of my head I can't think of an instance where that wouldn't be enough.
 
Upvote 0
Hi,
Okay, as promised a sample file for a no-vba method attached (we just use plain vanilla forms controls and defined names. Recommended!) Also a vba version - intercept the calculate event to capture changes and use the method recommended by Rory to update the list box accordingly. For the record, used the Object Browser and looked under OLEObject to get Listbox methods and properties.

SAMPLE FILE
sha256 checksum (zip file): 7acb449f6e718772a023028bec3e861d7fbf47581aaecd517b43ce7abea28e95

As you would expect, Book1.xlsx is the non-vba method and Book2.xlsm is the vba version.

EDIT:
I think I like AngelJ's suggestion better so for the VBA version here is a revision. We attached a macro to the Radio Button (actually, to both of them, but the same macro):
SAMPLE FILE
sha256 checksum (zip file):
54d02423ddcd6aa9fe4ac20824ede99d1efca8ca4cac4505610d0eb9fc19147c
Code:
Sub Change_Sheet1_Listbox_Source()
    
    If Sheet1.Range("_Option_Link") = 1 Then
        Sheet1.ListBoxes("List Box 4").ListFillRange = "Sheet1!_Data1"
    Else
        Sheet1.ListBoxes("List Box 4").ListFillRange = "Sheet1!_Data2"
    End If

End Sub
 
Last edited:
Upvote 0
@AngelJ- The code you had in post #10 works if you do .ListBoxes("MonthOrWeekList"). So many thanks for that!!

@xenou- First thank you for the note in post #11, I'll have to keep that in mind as I learn more about the Excel object model and begin using the MSDN reference materials. Now the code that you've provided. Well I tried setting up a similar situation, naming the ranges how you did, and all I did was change [Sheet1.] to [Sheets("Input").] but that comes back with a syntax error. Very peculiar. I'm also wondering if there's any benefit to coding it like that rather than just the single line AngelJ provided?


Also, one more thing that struck me as really odd. There are two different pieces of code that I can use that work in two different ways:
Code:
Sheets("Input").ListBoxes("MonthOrWeekList").ListFillRange = range("RD5:RD15")

AND   

Sheets("Input").ListBoxes("MonthOrWeekList").List = range("RD5:RD15").Value

If I use the first option, the listbox populates one single row, corresponding to cell RD5 i.e. the listbox would just show January. Literally, the Input Range in Format Control for the listbox would be "January". If I changed the range to "RD4:RD15" and in RD4 I put "RD5:RD15" then it would populate correctly! So really now what I'm wondering is, what is the difference between .List, .ListFillRange, and .RowSource? Why does excel treat them all so differently?
 
Last edited:
Upvote 0
List takes an array of values. ListFillRange and RowSource both require a String (the address of the range), so your first code should be:
Code:
Sheets("Input").ListBoxes("MonthOrWeekList").ListFillRange = "RD5:RD15"
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top