1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA problem

Discussion in 'Software Development' started by Rustyhj, Dec 1, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Rustyhj

    Rustyhj Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    56
    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
     
  2. Mr Red

    Mr Red

    Joined:
    Dec 1, 2008
    Messages:
    76
    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!
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  5. Rustyhj

    Rustyhj Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    56
    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:

  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  7. Rustyhj

    Rustyhj Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    56
    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
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  9. Rustyhj

    Rustyhj Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    56
    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
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  11. Rustyhj

    Rustyhj Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    56
    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 Files:

  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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([I]UserForm.Control.Value[/I])
    Set your control value to where specified above. That is the basis for Object Oriented Programming, after all.
     
  13. Rustyhj

    Rustyhj Thread Starter

    Joined:
    Aug 14, 2008
    Messages:
    56
    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:D
     
  14. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/774943