Page 1 of 4 123 ... LastLast
Results 1 to 15 of 52
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Picking-up User Form Entries (XP SP1)

    How can I transfer an entry from User Form to a speficied cell on a sheet? Also, is it possible to have a short-cut name and call a User Form with a control key + letter or call it some other way? Many thanks.

    F

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Picking-up User Form Entries (XP SP1)

    To answer only the latter part of your question, you will need to write a procedure in a regular code module that loads your userform. Then you can create a menu item and/or toolbar button and/or keyboard shortcut to call that procedure.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Thanks for that. How can I create a toolbar button?

    F

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Picking-up User Form Entries (XP SP1)

    > How can I create a toolbar button?

    Open the Customize dialog (Tools > Customize..., or right-click in the blank area to the right of your toolbars and choose it from the bottom of the context menu), click the Commands tab if it is not already open, then select Macros and drag your macro to a menu or toolbar. It will take on certain defaults (such as showing the macro name or a very basic icon). Without closing the Customize dialog, right-click your new item and you have lots of options for customizing it. One key: If you put an ampersand (&) in front of a letter or number, it becomes accessible using the Alt key. Unfortunately, in some applications such as PowerPoint and Outlook, this seems to be the only way to create a keyboard shortcut. Excel probably has more options (I hardly ever program Excel, so I'm not sure). Choose wisely, because many Alt+key combinations are already in use on one menu or toolbar or another.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Many controls on a userform have a ControlSource property. This can be set to a cell address, such as B37 or Expenses!B37 or 'Cost Sheet'!B37. Changing the cell value will automatically change the value of the control and vice versa.
    If you don't want that, you can use a command button on the form, say cmdOK, and save values to the worksheet in the On Click procedure:

    Private Sub cmdOK_Click()
    Worksheets("Cost Sheet").Range("B37") = Me.txtTravel
    End Sub

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Many thanks for all the help. I have another question. Is there an equivalent to User Forms but working the other way round? That is, a display equivalent that takes cells entries and reports in a User Form display type format? Thanks.

    F

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Picking-up User Form Entries (XP SP1)

    If you set the ControlSource of a text box on a userform to a cell address, the text box will automatically pick up the value of the cell entry.

    Or do you mean an automatically generated form? If your data are in table form, i.e. a first row with field names, and rows below it corresponding to data records, you can use Data | Form... A more powerful version is available at J-Walk Enhanced Data Form v2 (free).

  8. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Thanks for that. Is it possible to have a drop-down menu of macros on a customised menu button. I notice that there is 'custom menu item' but can't seem to attach more than one macro once i exit customise. Thanks.

    F

  9. #9
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Many thanks for that.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Select the "New Menu" category in the Categories list, then drag "New Menu" from the list of Commands to your toolbar. You can then drag multiple custom menu items to the new menu.

  11. #11
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Created New Menu but I can't seem to drag anything into it. There is also no right click capability to attach a macro. Must be doing something wrong.

    F

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Picking-up User Form Entries (XP SP1)

    You can assign a macro to a menu, but that's not what you want.
    After you have dragged New Menu to the toolbar, select Macros in the list of Categories in the Customize window.
    Drag Custom Menu Item to the new menu. When you hover the mouse pointer over the menu, the - as yet empty - menu will drop down, and you can place the new menu item there.
    Right-click the new menu item and select Assign Macro... from the popup menu.

  13. #13
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Great, got there! I should have been hovering over the lower 'box' that accompanies the the Main Menu icon. Really appreciate all of your help on this, thank you. As an aside, if I wanted to introduce my own button icons, is this possible?

    F

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Yes, you can right-click a toolbar button/menu item (while in Customize mode) and select
    - Change Button Image to select from a list of predefined images
    - Edit Button Image to start a simple icon editor.
    - Paste Button Image to paste a picture that has been copied to the clipboard. Button images are 16 x 16 pixels.

  15. #15
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    London, Gtr London, England
    Posts
    357
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking-up User Form Entries (XP SP1)

    Hi, I set the ControlSource reference to the cell I was interested in - it reports the result of an input from the userform in one cell and multiplies it by a number. The output result is reported on the useform but, the resulting formula is then over-written by the resulting answer. Not sure if this may have something to do with using the same userform to enter a number in and report the output on the same form.

    F

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •