Excel 2010 VBA - Issues with Drop Down box with Table Interface

[b4tmast]

First, I am a complete newbie still working on my very first VBA project. Any and all help will be truly appreciated. I have created a test app to be able to utilize some basic features. My current problem is that when the end user will use the drop down box, unless they leave (LostFocus) the drop down box it won't update the other fields. I initially tried using Change, but, that prevents me from being able to edit / save the fields. I currently have arrows that allow a user to go to First, Last, Previous and Next Fields. But, want them to be able to use the drop down box as a quick method to get to a record. Though this is a minor issue, it kind of defeats the purpose of having the drop down box. Any ideas as to how / what code I could use that would allow the updating of the other data fields without having to exit (LostFocus)? Thanks in advance and Happy Holiday to one and all.

[b4tmast]

Oh, to get the form to work, click the 'Cancel' button... Still working on that issue.

[SamT]

BeforeUpdate Event or AfterUpdate Event..

BeforeUpdate has a way to keep Focus on the control

[b4tmast]

I have tried both and the fields do not update. Perhaps I'm using it wrong. With LostFocus, at least when I leave the dropdown box, the other fields get updated. What am i doing wrong?

[VBA]
Private Sub cmbName_BeforeUpdate() 'LostFocus()
'*********************


On Error GoTo MyErrorHandler:
Call SortTable
Call FindRecorNumber
'Puts Vlookup results in respective cells


'If cmbName = Change Then


txtCity.Value = WorksheetFunction.VLookup(cmbName.Value, Range("TestTable"), 2, False)
cmbState.Value = WorksheetFunction.VLookup(cmbName.Value, Range("TestTable"), 3, False)
Range("P4") = WorksheetFunction.VLookup(cmbName.Value, Range("TestTable"), 4, False)
Range("Q4") = WorksheetFunction.VLookup(cmbName.Value, Range("TestTable"), 5, False)
'Me.lblRowNumber.Caption = 2
'Else
' Debug.Print "I'm here"
'End If


MyErrorHandler:
Call CheckEnabled




End Sub
[/VBA]

Is this right?

[SamT]

When you right-click on a CommandButton control on the worksheet, do you see an "Assign Macro" option? Or do you see a "View Code" option?

"Forms Toolbar" controls will have the Assign Macro Option. The Forms Toolbar controls are old tech (Excel5 and before) controls.

ActiveX controls (same as used in MS UserForms in VBA, will have the "View Code" option.



ActiveX controls
The control is "updated," when the User selects (clicks) an item in a control's list.

AfterUpdate Help:
Remarks
The AfterUpdate event occurs regardless of whether the control is bound (that is, when the RowSource property specifies a data source for the control). This event cannot be canceled. If you want to cancel the update (to restore the previous value of the control), use the BeforeUpdate event and set the Cancel argument to True.
The AfterUpdate event occurs after the BeforeUpdate event and before the Exit event for the current control and before the Enter event for the next control in the tab order.

Private Sub ComboBox1_AfterUpdate()

End Sub

BeforeUpdate Help:
Remarks
The BeforeUpdate event occurs regardless of whether the control is bound (that is, when the RowSource property specifies a data source for the control). This event occurs before the AfterUpdate and Exit events for the control (and before the Enter event for the next control that receives focus).
If you set the Cancel argument to True, the focus remains on the control and neither the AfterUpdate event nor the Exit event occurs.

Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

[b4tmast]

I built a practice worksheet with just a combobox and a text box trying to utilize the ActiveX controls AfterUpdate & BeforeUpdate. I can not get either to work. When I went to MSDN.MS.... Office Dev Center, it says it's for version 2013 - didn't show earlier versions (I'm on 2010). Could this maybe be why I can't get it working? Or am I doing something fundamentally wrong?

[VBA]
Sub combobox1_AfterUpdate()


MsgBox "AfterUpdate"




End Sub
Sub combobox1_BeforeUpdate(ByVal Cancel as MSForms.ReturnBoolean)


MsgBox "BeforeUpdate"


End Sub
Sub combobox1_lostfocus()


MsgBox "LostFocus"


End Sub


Sub combobox1_change()


Dim text1 As String
text1 = ComboBox1.Value
TextBox1.Value = text1


End Sub
[/VBA]

[SamT]

I would look into the security settings of Excel (the workbook.)

BTW, I am on XP and the Helps I pasted are from it.

In my Excel, I Trust the VBA Project.

I also use Add-Ins and have to set the general Security Level to "Low" in order to open all the attachments on the Forum.