+ Reply to Thread
Results 1 to 16 of 16

Unloading and Showing UserForms problem

  1. #1
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Unloading and Showing UserForms problem

    Hi All,

    I Have a Spreadsheet that uses 2 Userforms. Userform1 and Userform2.

    The main Userform is Userform1.

    If a certain textbox on Userform 1 is Filled with a specific Value type in this case Numbers then Userform1 Unloads, Userform2 loads and the activecell is offset to another cell (This must happen everytime userform2 is used) once the Textbox in UserForm2 has been filled it unloads and Userform1 reloads to carry on.. this can happen consistantly.

    Code for the TextBox in Userform1 to Unload and show UserForm2:

    Please Login or Register  to view this content.
    Code for UserForm2:

    Please Login or Register  to view this content.
    This works for the First instance that UserForm2 is shown but when it is called again the offset doesn't work.

    Can anyone tell me why?

    Any help would be greatly appreciated.

    Regards

    JRidge

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Unloading and Showing UserForms problem

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Unloading and Showing UserForms problem

    Hi Norie,

    I am Using a Hand Scanner or Keyboard + Enter to enter the data in the Fields of the Userforms.

    Regards

    JRidge
    Attached Files Attached Files

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Unloading and Showing UserForms problem

    What's the purpose of the 2nd form?

  5. #5
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Unloading and Showing UserForms problem

    This is an example of what it should do and what it actually does on the second time the Userform2 is called.

    Userform1 enters in the cells B:F

    TextBox1 = B (Alphanumeric) example MW-01-001-01
    TextBox2 = C (Numeric) Example 1234567890
    TextBox3 = D (Alphanumeric) Example F123
    TextBox4 = E (Numeric) Example 9087654321
    TextBox5 = F (Numeric) Example 654

    If instead of Alphanumeric a Numeric number is entered in TextBox 1 Example 1234567890

    The system knows that another container is located in the same location as the line before.
    It Adds a new Line above the current Row, and pastes the 1234567890 in the E column of the new Row and then automatically moves 1 column right.

    Userform2 then opens to add the Qty number to column F but because this action moves the activecell to the start of the next process i am moving it back to the cell that would receive the Qty.

    This works for the first time that the Userform2 is called, but when called a second time the offset doesnt work again and the activecell stays in the cell on the next row at the start of the process.

    I don't know why it works the first time but not the second..

    Hope this makes sense.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Unloading and Showing UserForms problem

    Hope this makes sense
    If the solutions provided within this Thread takes care of your question:

    Please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

    You can also click on * (star) Add Reputation to add reputation to those who have put their time and efforts to help you in this Thread.

    This is another way to say Thank You to them.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Unloading and Showing UserForms problem

    Do you really need the second userform?

    Couldn't you just have an input box pop up for the user to enter the quantity?

    Or, alternatively, add hidden textboxes for the quantities to the right of the existing textboxes.

    When a numeric value is entered in the main textbox you can make the corresponding quantity textbox visible for user entry.

  8. #8
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Unloading and Showing UserForms problem

    Hmm nice idea....

    How would i do that

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Unloading and Showing UserForms problem

    Perhaps something like this.
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Unloading and Showing UserForms problem

    Looks Good.. Except.

    TextBox1.Value is also cleared so
    Please Login or Register  to view this content.
    is Blank.

    Can we say all Textboxes except TextBox1 = vbNullString. And then clear box at the end.

    Also once InputBox has closed the Cursor is in Textbox 2 on the userform and should start in Textbox1 again.

    But alot neater than mine

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Unloading and Showing UserForms problem

    You don't actually need TextBox1.Value into the input box message, I just thought it was a nice touch.

    If you want to keep it move the code that clears the textboxes after the code for the input box.
    Please Login or Register  to view this content.
    Or if you don't want the textbox cleared at all try this.
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Unloading and Showing UserForms problem

    Tried a mixture of Both:

    Please Login or Register  to view this content.
    Seems to be working...

  13. #13
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Unloading and Showing UserForms problem

    Glad it worked but I'm pretty sure you don't need to mix the code up.

  14. #14
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Unloading and Showing UserForms problem

    Or like this:

    Please Login or Register  to view this content.
    How could i say: If Active cell is in Column F then Offset activecell by (,-1) or words to that effect...

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Unloading and Showing UserForms problem

    Do you mean you want to move the active cell or do you want to put something in a cell offset from the active cell?

  16. #16
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    265

    Re: Unloading and Showing UserForms problem

    Please Login or Register  to view this content.
    This seems to do it..

    If activecell isnot in Column 5 then delete contents in the cell to the left and set that cell as the active cell..

    Not neat but seems to work

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Unloading a Form
    By VBA Fun in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2019, 12:52 AM
  2. Multipage problem with userforms
    By zhenry in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-06-2014, 11:00 AM
  3. [SOLVED] Bug evading me in showing multiple userforms
    By zaimor in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-06-2013, 06:06 PM
  4. userforms showing and hiding
    By Laurin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-20-2005, 02:10 PM
  5. Unloading a form using the ESC key
    By johncassell in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-16-2005, 01:19 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1