combo box dependancy

[snowmantle]

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??

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

 

[Enkay62]

Hi.
Just a suggestion "on the fly".
Add to your "If" istructions the false condition.
That is:

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
Else
Me.cboAmount.value = ""
End If
Next Row
End Sub

(add it to both comboboxes)


Try it and let me know if it works.
If not I'll do some tests on my pc and give you a more precise solution.
Hope this helps.
By
Nick

 

[fumei]

if a user enters in the amount and doesnt exit from the combobox to update it, it wont work.

Yes...but are they going to sit there and look at the combobox forever? They are going to do something.

Unless they sit there and never do anything else...including closing the file...there HAS to be another event that you can use to update.

Gerry

 

[Enkay62]

Hi Snowmantle.
Here I'm again.

There's a little correction about the code I posted befor.
This is the correct one.


Private Sub cboAmountWords_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

Exit for

Else
Me.cboAmount.value = ""
End If
Next Row
End Sub

(add it to both comboboxes.....of course changing properly the objects' names)

Bye
Nick

 

[Enkay62]

Hi Snowmantle.
Here I'm again (hope for last time about this thread).

I looked at your code and, all in all, I would write it in a cleaner way, as following (just for one combo. for the other one do the same changing properly the combo names)

Private Sub cboAmountWords_Change()
For Row = 1 To LastRowAmount
if cboAmount.Value = Sheets("Amount").Cells(Row,2).Value _ Then
cboAmount.Value = cboAmountWords
Exit for
Else
cboAmount.value = ""
End If
Next Row
End Sub

This should work

Bye

Nick

 

[Enkay62]

Hi sSnowmantle.
Forget my last post.

Private Sub cboAmountWords_Change()
For Row = 1 To LastRowAmount
If cboAmountWords.Value = Worksheets("Amount").Cells(Row, 2).Value Then
cboAmount.Value = Worksheets("Amount").Cells(Row, 1).Value
Exit for
Else
cboAmount.value = ""
End If
Next Row
End Sub

SORRY.
bYE
nICK

 

[snowmantle]

Hi, yea ur right Gerry

They click a confirm button, so i could display an error message telling the user the amountinwords no longer matches the amount in cash and to change it, but it wasnt really what i wanted.

Thanks for your comments Nick,

Ill have a look at your code and change mine accordingly.

 

[snowmantle]

The problem with using your code Nick is that if the user enters their own amount that doesnt match what is in the drop down it will blank out the cboAmount.value

As cboAmount.Value has now changed to "" and "" isnt a selection in the drop down cboAmountWords.Value also sets itself to "".

 

[snowmantle]

The only thing i can think of making this work for custom amounts as well, is to have the drop downs only allow for values in the drop down and nothing else, but have a button called 'custom' which when clicked will allow for custom amounts and not auto change the combo box values.