[Rustyhj]

Hi
Once again I 'm having a problem with VBA in excell. I altered a code to meet my needs. I need to use the userform to be completed by user but the data should be unloaded onto 12 monthly sheets (jan, feb, and so on).
Should I use 12 different userforms or what is the best option. I'm adding part of the code but if you prefer I could forward the full spread sheet to be used
Thanks in advance, looking forward to a reply
the code :

Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("jan").Activate

Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Thanks
Rusty

[Mr Red]

I would use drop-down combo boxes on forms. On click go to the sheet for data entry and leave the user in the sheet.

What I do for several of my files is, on activation of a form, load from clip (could be from anywhere) onto a textbox and the get a word at a time with a button and put it to a second textbox. A combobox below that has a list of "meanings" one of which which is selected by the user and a "put" button relates the meaning to the cell location and puts it there. Some meanings hide/show comboboxes (eg day or month) which can further be clicked on.

The initial difficulty is finding the " get from Cliboard" code - if you have loaded VBA help it can be found. If not - doit!

[OBP]

Rusty, for future Threads, there are some very good Excel programmers on the "Business Applications" forum, so you will probably get even more responses on there.

[Rollin_Again]

I agree that using the same form with a combobox is the best way to go. You can add logic to default to the current month or the user can manually change the value to specify another worksheet.

Regards,
Rollin

[Rustyhj]

Hi Mr Red
Thanks for replying to my problem. I did not "doit" as you surgested cause I had it and tried it "put DO in clipboard" it worked well but is not what I'm looking for, therefor I'm attaching my full project to this reply, if you would be so kind as to have a look at it and make some changes please.
Just to recap, a 'booking form' is created on the 1st sheet "jan" (january), when completed the info is updated onto that sheet, I only get it to update on the same sheet "jan" even if I create a form on each of the other months aswell. So each month should have its own sheet with data for that month, the calculations I will transfer later to other sheets. I trust that my explenation is better now
Thanks in the meantime
Rusty

[Rollin_Again]

The first line of code you are using is in your OK button click event is ActiveWorkbook.Sheets("jan").Activate

You can remove this line altogether since focus will already be set at the correct worksheet. You don't need to activate a particular sheet by it's name if it is already the active sheet.

Regards,
Rollin

[Rustyhj]

Hi Rollin
I say Thankyou so verymuch, a small problem causes headaches, so thanks again for guys like you to help out with guys like us not so cludeup.I did what you said and no problem working great
I just have one question before I mark this as solved, if I got a combo or listbox for the 12 months can those months be used as hyperlinks to the month sheets as in my project and how?
Thanks again
Rusty

[Rollin_Again]

Controls such as comboboxes or listboxes cannot contain actual hyperlinks. If you want to be taken to a particular sheet when clicking a value in the combobox or listbox you will need to use the combobox or listbox change event to fire additional code to evaluate the newly made selection and then take you to the appropriate sheet.


Regards,
Rollin

[Rustyhj]

Hi Rollin
Sorry for only replying now.
Yes to your question if it does reside to the combo/listbox, dont know which would be best, but I see your reply above says that I should use a change event, any idea of how it is done, maybe a short version of the macro?
Rusty

[Rollin_Again]

Explain again to me why you want a combobox or listbox? The way your project is set up right now you are calling the same data entry form from each of the worksheets. If the entry form for each worksheet will be the same anyways you can keep it the way it is. If you want to make any changes please explain in detail how the form will be set up including it's functionality.

Regards,
Rollin

[Rustyhj]

Hi
I did not explain correctly earlier, sorry, to the program I'm adding exstra sheets, main menu, stats and graph sheets which I'll add on later, but on the mainmenu sheet you'l have selections referring to the different sheets, and also directing user to the different months (which I'd like a combo/list box) to open userform and adding data. Going to try and attach a copy of the menu. I think this should explain, appologies for being a nuisance.
Thanks Rusty

[Zack Barresse]

If you want to use the value of a combobox or listbox as a "hyperlink", just use that [text] value as the string reference for the specified object, i.e. ...

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(UserForm.Control.Value)

Set your control value to where specified above. That is the basis for Object Oriented Programming, after all.

[Rustyhj]

Hi Zack
Thanks for returning with a helpfull tip on my question it was a big help after thinking that the previous person did not reply. Thanks again and Seasons Greetings to you and yours
Rusty