Replies

Hi Hans,

Thanks for the pointer. Strange is that my References does not have this Object???
Where can I find this? Does this come together with Office 2003?

Hi John

Thanks for the link. Where do I put the codes of your? I have put it in the Outlook’s ThisOutlookSession but when I try to run it, it show an error, Compile Error : User define type not defined and highlighted Dim objData As DataObject. How does the codes work.?

Thanks

Hi Hans,

Thank you for the solution. Is it impossible to perform a lookup for the case 2?
How can I tell when to use the combination of Vlookup together with Index and Match

How do I modify the formula into macro codes?

Thank you for your guidance.

Hi Rory,

This doesn’t seem to work for me. How do you find the Entry ID of the message?

Error msg show : Unable to display selected folder or item…. (Don’t know how to insert the error picture here)

If I want to show msg that resides in sub folders, is it possible?

Thanks

Hans,

You should have told me earlier so that I don’t waste approximately 7 hrs last week to look up on how to produce error msg.
I will try on this and post back next week. Meanwhile, can you point out which line/s of your codes create the unique list.

Thanks, Appreciate your assistance and guide.

Hi Hans,

Thanks for trying to help. Your suggestion of using ComboBox is a good idea but I can’t use it in this because I will need to re-create more than 500 entries in Sheet 2 to hold the unique items. I am doing this in order to save time and manual work but its seem like the opposite here.

In the past, you have assist me and guide me to learn while I do my simple stuff and I thought it was a great forum.
Of course, It is at your discretion. Anyway, thanks for all your help.

Hi Hans,

Thanks for the guide. However, the txtbox for TxtSI don’t function as expected. I may not be able to use this as there are at least more than 500 different ac to be create in sheet 2.

After about 7 hours of reading, testing and surfing the net. I have solved out the error message part, but my Vlookup doesn’t work for Currency. Its list detail in the SI textbox even the account don’t have that Currency. How can I use the Vlookup with reference to the data in Col A and Col C to produce Col D in the Form. Can you assist in this code.

Thanks

Hi Hans

Thanks, I will try on the autofilter however, I still would like to know how to do what you have mentioned above changing the txtboxes to comboboxes and Initialize event with the available values.

thanks in advance

Hi Hans,

I know about the Auto and advance filter functions, but I need something where it is easy for the users and I can’t assume that
all will know how to use this. Moreover, this workbook / sheet will be protected and a read only file.

Coming back to this, I am at a loss how to use the ComboBox and the Initialize event. I have read from MS site, VB Help and JW’s book, but still confuse how to use it. . Appreciate that you shed some light on it as I am still learning VB and its beyond my level currently. Thanks

Attached the latest file.

Hi Hans,

Thanks. I have managed to complete the codes for the form. However, I noticed that if a user input a wrong ac,
it won’t show an error message, such as ” Invaild Account”. Can you help with the codes.

Thanks

Hi

I have change the codes However, there are some which encounter some problem. The Form don’t show what I want when I click the Exit button.
Private Sub ExitButton_Click()
‘exits the system’
Dim response As String
response = MsgBox(“Are you sure you wish to exit?”, VbMsgBoxStyle.vbYesNo, “check selection”)
If response = vbYes Then
Me.Unload

End Sub

The Vlookup for Currency produce error 13 : type mismatch after I type in a currency code, such as USD
code :
Private Sub txtCurr_AfterUpdate()
With txtCurr
TxtSI.Text = Application.VLookup(.Text, Worksheets(“SI”).Range(“A:D”), 4, False)

End With

End Sub

I am loss as to where did I go wrong. Would appreciate your help.

Hi Hans,

Generally every business will have a normal Office appl. but due to some policies, my organisation doesn’t allow that.

Thanks for the suggestion.

Hi Rory,

The blank row actually refers to the account no above, and yes its possible, I just need to copy down.

Hi Hans

Unfortunately, my office does not use Access. Morover, This sheet contain about 1000 entries, hence the idea to create the Form.

Hi Hans,

Great ! It works accordingly. Thanks for the tip on Excel.