Userform help: optionnal output

[edtrash]

Hi all,
this is my first time posting here, as well as my first vba project. I've only been introduced to the topic recently, and am starting to try my hands at it.

The workbook I created is intended as a list for the concerts I attended.
A button pops a userform asking for date, venue, bands, and curator if there's any. The code was scraped from various forums of the web + my tiny understanding of vba.

My problem lies on the venue, for which I have two input methods: the first one is a simple text box, the second one is combo box pulling from a list on a hidden worksheet.
My code so far in the "OK button" of the userform, referencing to the venue output, is:

Please Login or Register  to view this content.

This is broken because it only acknowledges one of the two outputs, I've not really understood while troobleshooting why it picked sometimes the textbox and sometimes the cbbox, while I touched the code a bit randomly.

Attaching my entire workbook it it helps more, feel free to tell me if you find something too amateur-ish to be true! (see FormatMacro, kinda ashamed of this one.. as well as the Application.Run in the OK button, needs adjusting if the workbook is renamed...)


On a sidenote; I was wondering if there was any way to make the InputDate text box to automatically add slashes when typing, i.e. inserting a slash after typing the two first numbers, and again after the two following numbers, inside the userform, keeping them on the actual output (am I clear here? If not nevermind).

Thanks to all!
ED

[protonLeah]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[edtrash]

Thanks for your response Ben; however I must admit that I'm a little lost here, as to what your code suggestions do and where I should put them.

If the first piece is for the "/ in date" bit, where should I add it so that they are added while typing in the userform?
The second code leaves me speechless. If it's a sub, where/when should it start, and what does it do?
Third one is for the actual venue output, when should this be run?

Again, thanks
ED

[protonLeah]

The first two are additions to your userform code. Just copy them to the bottom of userform module.
the first one, InputDate_Change(), is activated each time you type in the date input textbox. if the length of the current entry is is 2 or 5 then it adds a"/" to the end of the string. and returns to the textbox for further characters.

The second one, InputDate_KeyUp, also, is activated each time a key is released when typing in the date box. The code checks to see if the Enter or Tab key is pressed and jumps to the venue box.

in the cmdOK_Click() you have the code lines:

ws.Cells(iRow, 2).Value = Me.InputVenue.Value If InputVenue.Value <> 0 Then ws.Cells(iRow, 2).Value = Me.cbVenue.Value End If

These lines are to be replaced by:

Please Login or Register  to view this content.

This code uses the VBA function IsEmpty to test for data in the Venue box. If it is empty then the combobox must hold the data. IsEmpty returns TRUE to the Select Case statement and it branches to the TRUE case to copy the data from the combobox instead of the textbox.

OOPs: I changed the variables irow to NextRow and ws to CONCERT (only because I like more descriptive names), but I forgot to change them back before posting.

[edtrash]

Ben, thanks for your explanations;

however, after following your instructions, I found that the problem remains, leaving the InputVenue blank and choosing from the cbbox does not translate to the wsheet. Did it work on your side? Using your file attachment it replies an error on the Application.Run part, regardless of my attempts to modify it ...

Also, I'm finding that pressing enter to accept, and the tab key now have mixed results, focusing on InputVenue, where I'd rather keep it on Date. I prefered deactivating your second bit of code!

Getting there! thanks

[protonLeah]

o.k. try this one:

[edtrash]

Ben, thanks for this, all seems to work now.
You've been very helpful! cheers.

Kind regards, ED