snowmantle
Programmer
Hi,
I have setup 2 combo boxes, one for amount in words
eg. TenThousandPounds
and one for the amount in £'s eg. 10000.00
The combo boxes have 4 different choices and the user has the ability to type their own amounts in as well.
The problem i have is that if someone enters in TenThousandPounds it will update the other box with 10000.00 and vice versa, but if they decide to change it to their own choice and dont hit return or click out of the combo box again the 10000.00 is not blanked out.
I first had these functions work on Change() but that was causing problems, i now have them setup to run AfterUpdate(), but if a user enters in the amount and doesnt exit from the combobox to update it, it wont work.
Either way its not full proof, does anyone know a way around this??
I have setup 2 combo boxes, one for amount in words
eg. TenThousandPounds
and one for the amount in £'s eg. 10000.00
The combo boxes have 4 different choices and the user has the ability to type their own amounts in as well.
The problem i have is that if someone enters in TenThousandPounds it will update the other box with 10000.00 and vice versa, but if they decide to change it to their own choice and dont hit return or click out of the combo box again the 10000.00 is not blanked out.
I first had these functions work on Change() but that was causing problems, i now have them setup to run AfterUpdate(), but if a user enters in the amount and doesnt exit from the combobox to update it, it wont work.
Either way its not full proof, does anyone know a way around this??
Code:
Private Sub UserForm_initialize()
LastRowAmount = ThisWorkbook.Worksheets("Amount").Range("A65536").End(xlUp).Row
LastRowAmountWords = ThisWorkbook.Worksheets("Amount").Range("B65536").End(xlUp).Row
cboAmount.Value = ""
cboAmountWords.Value = ""
With cboAmount
For Row = 1 To LastRowAmount
.AddItem Worksheets("Amount").Cells(Row, 1)
Next Row
End With
With cboAmountWords
For Row = 1 To LastRowAmountWords
.AddItem Worksheets("Amount").Cells(Row, 2)
Next Row
End With
mydate.Value = FormatDateTime(Now(), vbShortDate)
End Sub
'XXXXXX DO NOT USE THESE FUNCTIONS BECAUSE THE FORM DOESNT AUTO CHANGE FOCUS AFTER CHANGING
'THE COMBO BOXES VALUES, A SECOND CHANGE WOULD RESULT IN A FIELD STAYING THE SAME IF THE USER
'DOESNT CLICK OFF THE BOX TO UPDATE IT XXXXXXXXXXXXXXXX
'
'Private Sub cboAmount_Change()
'LastRowAmount = ThisWorkbook.Worksheets("Amount").Range("A65536").End(xlUp).Row
'For Row = 1 To LastRowAmount
' If cboAmount.Value = Worksheets("Amount").Cells(Row, 1).Value Then
' cboAmountWords.Value = Worksheets("Amount").Cells(Row, 2).Value
'End If
'Next Row
'End Sub
'Private Sub cboAmountWords_Change()
'LastRowAmountWords = ThisWorkbook.Worksheets("Amount").Range("B65536").End(xlUp).Row
'For Row = 1 To LastRowAmountWords
'If cboAmountWords.Value = Worksheets("Amount").Cells(Row, 2).Value Then
' cboAmount.Value = Worksheets("Amount").Cells(Row, 1).Value
' End If
'Next Row
'End Sub