Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I know you have accepted a solution now that will work for you and is much simpler than the below, but if you're interested, this would be the more direct answer to your question.</p> <p>You need to create a class to handle the button clicks, so every time the button is clicked it uses the event in the class, you only need to do this once then create a new instance of it for every button. To stop these classes going out of scope and being lost, they need storing in a class level declaration. In the below I've moved your code around a little.</p> <p>In the class module (I've called it cButtonHandler)</p> <pre><code>Public WithEvents btn As MSForms.CommandButton Private Sub btn_Click() MsgBox btn.Caption End Sub </code></pre> <p>With events is used as it allows you to use most of the events for the control. I've moved the button generation code into the userform as below:</p> <pre><code>Dim collBtns As Collection Private Sub UserForm_Initialize() Dim theLabel As Object Dim labelCounter As Long Dim daycell As Range Dim btn As CommandButton Dim btnCaption As String 'Create a variable of our events class Dim btnH As cButtonHandler 'Create a new collection to hold the classes Set collBtns = New Collection For Each daycell In Range("daylist") btnCaption = daycell.Text Set theLabel = ReadingsLauncher.Controls.Add("Forms.Label.1", btnCaption, True) With theLabel .Caption = btnCaption .Left = 10 .Width = 50 .Top = 20 * labelCounter End With Set btn = ReadingsLauncher.Controls.Add("Forms.CommandButton.1", "runButton", True) With btn .Caption = "Run Macro for " &amp; btnCaption .Left = 80 .Width = 80 .Top = 20 * labelCounter 'Create a new instance of our events class Set btnH = New cButtonHandler 'Set the button we have created as the button in the class Set btnH.btn = btn 'Add the class to the collection so it is not lost 'when this procedure finishes collBtns.Add btnH End With labelCounter = labelCounter + 1 Next daycell End Sub </code></pre> <p>Then we can call the useform from a separate routine:</p> <pre><code>Sub addLabel() ReadingsLauncher.Show vbModeless End Sub </code></pre> <p>Classes in VBA aren't particularly well covered in many VBA books (generally you need to read VB6 books to get an understanding), however once you understand them and how they work, they become incredibly useful :) </p> <p>Hope this helps</p> <p>EDIT - to address additional queries</p> <p>To refer to objects in a collection, this is either done through the key or the index. To use the key, you need to add it as you add the item to the collection, so:</p> <pre><code>collBtns.Add btnH </code></pre> <p>Would become</p> <pre><code>collBtns.Add btnH, btnCaption </code></pre> <p>For this reason, keys must be unique. You can then refer as follows:</p> <pre><code>'We refer to objects in a collection via the collection's key 'Or by it's place in the collection 'So either: MsgBox collBtns("Monday").btn.Caption 'or: MsgBox collBtns(1).btn.Caption 'We can then access it's properties and methods 'N.B you won't get any intellisense collBtns("Monday").btn.Enabled = False </code></pre> <p>You can also add additional properties/method to your class if required, so for example:</p> <pre><code>Public WithEvents btn As MSForms.CommandButton Private Sub btn_Click() MsgBox btn.Caption End Sub Public Property Let Enabled(value As Boolean) btn.Enabled = value End Property </code></pre> <p>Would then be accessed:</p> <pre><code>collBtns("Monday").Enabled = False </code></pre> <p>Does this help? For further reading I would point you towards Chip Pearson's site, he has great stuff on most topics <a href="http://www.cpearson.com/excel/Events.aspx" rel="nofollow noreferrer">http://www.cpearson.com/excel/Events.aspx</a></p> <p>Just remember that VBA is based on VB6 so is not a fully fledged OO language, for example, it does not support inheritance in the normal sense, only interface inheritance</p> <p>Hope this helps :)</p>
    singulars
    1. This table or related slice is empty.
    1. POAssign event handlers to controls on user form created dynamically in VBA
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USSWa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POAssign event handlers to controls on user form created dynamically in VBA
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks heaps for the in-depth reply. A still tossing whether to use the user-form or the sheets, but this is an excellent response, particularly as you mention, classes seem to be avoided (or super technically explained). I changed your answer to the actual one as it is the one that actually answers the question
      singulars
      1. PO
      1. USBiGXERO
    2. COYour answer has given me the confidence to (once i have finished the current sheet based solution) to attempt the user forms. 2 questions that i think will help myself and other noobs alike, 1) If i want to refer to a particular btn in the collection (e.g. to change the caption or to make it invisible) later on, can i refer to it using a collections reference (e.g. collBtns.btn[2]) 2) Do you know any online resource that explain collections, event handlers, etc. Having a noob java background i like the idea of being more object oriented and less event driven
      singulars
      1. PO
      1. USBiGXERO
    3. COI've changed the above to answer your questions
      singulars
      1. PO
      1. USSWa
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload