Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Software Development Software Development
Search Search
Search for:
Tech Support Guy > > >

Solved: VBA problem


(!)

Rustyhj's Avatar
Rustyhj Rustyhj is offline
Member with 56 posts.
THREAD STARTER
 
Join Date: Aug 2008
01-Dec-2008, 01:14 PM #1
Solved: VBA problem
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's Avatar
Mr Red Mr Red is offline
Computer Specs
Member with 76 posts.
 
Join Date: Dec 2008
02-Dec-2008, 07:46 AM #2
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,666 posts.
 
Join Date: Mar 2005
Location: UK
02-Dec-2008, 03:26 PM #3
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's Avatar
Member with 4,700 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
04-Dec-2008, 12:04 PM #4
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's Avatar
Rustyhj Rustyhj is offline
Member with 56 posts.
THREAD STARTER
 
Join Date: Aug 2008
04-Dec-2008, 01:00 PM #5
VBA Problem
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
Attached Files
File Type: xls Bookingsversion2b.xls (69.5 KB, 218 views)
Rollin_Again's Avatar
Member with 4,700 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
04-Dec-2008, 04:51 PM #6
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's Avatar
Rustyhj Rustyhj is offline
Member with 56 posts.
THREAD STARTER
 
Join Date: Aug 2008
06-Dec-2008, 04:36 AM #7
VBA Problem
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's Avatar
Member with 4,700 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
06-Dec-2008, 08:51 AM #8
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's Avatar
Rustyhj Rustyhj is offline
Member with 56 posts.
THREAD STARTER
 
Join Date: Aug 2008
06-Dec-2008, 11:33 AM #9
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's Avatar
Member with 4,700 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
06-Dec-2008, 02:03 PM #10
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's Avatar
Rustyhj Rustyhj is offline
Member with 56 posts.
THREAD STARTER
 
Join Date: Aug 2008
07-Dec-2008, 06:31 AM #11
VBA problem
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
Attached Thumbnails
Solved: VBA problem-main-menu-jpeg.jpg  
Zack Barresse's Avatar
Computer Specs
Member with 5,434 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
11-Dec-2008, 01:09 PM #12
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. ...

Code:
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's Avatar
Rustyhj Rustyhj is offline
Member with 56 posts.
THREAD STARTER
 
Join Date: Aug 2008
14-Dec-2008, 06:16 AM #13
VBA Prolem
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
macro

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2