0

I have a page in a multi page form which takes input from a text box and check box and adds this into a multi column list box below it when a submit button I clicked. I then want the user to be able to select a row in the list box and display the contents in the text box and check box for editing.

I can add multiple text box and check box values into the list box using the submit button, but it does not select a row when I click on the list box item. It just does nothing. It looks like the focus is not being transferred.

Would really appreciate it if someone could give me some ideas or guidance on how I may solve this? The code for the two actions are:

Private Sub cmdaddcontrol_Click()

If Not txtbox_Ctrl_Desc = "" Then 'check for input into text box
   ctrl_listbox.AddItem 'add items to list box
      ctrl_listbox.List(ctrl_list_count, 0) = ctrl_list_count + 1 'add row number in list box column
      ctrl_listbox.List(ctrl_list_count, 1) = txtbox_Ctrl_Desc.Value 'set list box column to text box contents
   If Not chkbox_ctrl = False Then 'check if check box is selected then insert appropriate value into listbox column
      ctrl_listbox.List(ctrl_list_count, 2) = "Y"
   Else
      ctrl_listbox.List(ctrl_list_count, 2) = "N"
   End If
   ctrl_list_count = ctrl_list_count + 1 'increment listbox row counter
   txtbox_Ctrl_Desc = "" 'reset text box to blank
   chkbox_ctrl = False 'reset check box to blank
Else
   MsgBox "You have not enetered anything in the control box" 'error message if no control description in text box
End If

End Sub

Private Sub ctrl_listbox_Click()
Dim i As Integer

'find the selected list item
i = ctrl_listbox.ListIndex
ctrl_listbox.Selected(i) = True

'add the values to the text and check boxes above the list box
txtbox_Ctrl_Desc.Value = ctrl_listbox.Column(1, i)
If Not ctrl_listbox.Column(3, i) = "  Y" Then
   chkbox_ctrl.Value = True
Else
   chkbox_ctrl.Value = False
End If

End Sub
Digger
  • 1
  • 1
  • Have you verified the `ctrl_listbox_Click` event launches when you click your listbox? You can either set a breakpoint or add a `Debug.Print "ctrl_listbox_Click"` to the sub. – Inarion Apr 26 '18 at 09:36
  • @Inarion, yes I have checked this and this seems to be the problem. For some reason the 'ctrl_listbox_Click' event is not launching. Any idea why this may be? – Digger Apr 26 '18 at 11:23
  • The first thing to check then would be the assigned macro. Right-click the listbox, select *Assign macro* (or smth similar) and verify it actually calls the correct procedure. – Inarion Apr 26 '18 at 12:55
  • Thanks @Inarion. There is no Assign Macro option on right click. The nearest is view code, which takes me directly to the ctrl_listbox Click event code section (and everything is spelt correctly). The problem appears to be that this event handler is not being called when I click the list box during run and I do not know why. The naming is correct for the event. Is there anything else that could stop the event being called?? – Digger Apr 26 '18 at 13:27
  • Are you possibly using an *ActiveX listbox*? What I was talking about is a *form listbox*. Is there a specific reason why you chose that type of listbox over the other? – Inarion Apr 26 '18 at 13:38
  • Unknowing whether you accidentally or purposely used an ActiveX control, I'll just leave [this link to a somewhat related question here](https://stackoverflow.com/questions/15455179/what-is-the-difference-between-form-controls-and-activex-control-in-excel-20). – Inarion Apr 26 '18 at 13:42
  • @Inarion, I was purposefully using ActiveX controls in the VBA forms design as I find them easier to work with and there are more properties. So yes I was using an ActiveX listbox, all the forms controls are ActiveX. All I am trying to do is: 1) enable user to enter text in text boxes 2) add text box info to a multi-column listbox and display it on the screen, 3) allow user to either a) enter more text in text boxes b) select row in list box c) move on to another screen/section. The code does 1, 2 & 3a) & 3c) - but for some reason I cannot do 3b). – Digger Apr 30 '18 at 06:35
  • Thanks for the link explaining the difference between normal forms and ActiveX forms. This piece of functionality is actually just a small part of a reasonably complex and detailed multi-page user form so I felt it was too complex for a normal Excel form, there is also the greater flexibility with the ActiveX design. Just not sure if the problem I have is something to do with my coding, or a bug in VBE/Excel. Do you have any other thoughts or ideas to try and fix this by any chance? – Digger Apr 30 '18 at 06:45
  • The only way I could reproduce the symptoms you described was by setting `ctrl_listbox.Enabled = False`. Also with only one list element available, the `ctrl_listbox_Click` event will only register once (on the first click) - after that you can't trigger it without adding more elements to the list. Aside from that, I'm out of ideas... – Inarion Apr 30 '18 at 09:19
  • @Inarion, happy to send a copy of workbook so you can see if the same happens with you. You'll then be able to see the other code around it which may help. – Digger May 01 '18 at 06:20
  • @Inarion thank you for all your help and assistance on this little problem. I have managed to find the cause of the problem and resolve it. On review I discovered I had set the 'locked' property for the listbox. Hence I could write to the listbox but not select from it - a stupid, novice error. However, without your last comment I most probably would not have thought to comb through all the code to check. So thank you. – Digger May 01 '18 at 10:59
  • Heh, guess why I posted that comment... I had exactly that causing me trouble (at least) once. Stumbling upon it after hours of searching made me really want to bang my head against something hard. :) Glad it helped! – Inarion May 02 '18 at 13:51

1 Answers1

1

After finding the cause of the issue in the comment chain of the question, I'd like to persist the solution here as an answer for the people that might come across the question in the future:

  1. Make sure that the .Enabled and .Locked properties of the misbehaving ActiveX control are set correctly. (True if the control should allow manipulation/activation by the user at runtime. False if you want to deny the user interaction with the control.)
  2. Verify that the event procedure has the correct name. Maybe you changed a controls name? The correct naming scheme for event procedures is controlName_eventName(), e.g. ListBox1_Click(). An empty _click() procedure is generated when you select Show code in the control's context menu in Design Mode.

Although seemingly trivial, sometimes simple details escape our attention - even more so in complex projects. It's often helpful to have someone from outside the project have look at your problem. In their innocent ignorance of your project's inner workings, they might just be able to ask the right questions that lead to a quick solution.

Inarion
  • 578
  • 3
  • 14