Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    East Tennessee
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Wont accept Input (Excel 2000)

    I've made a user form that inputs data to a worksheet, and that part is working OK. But after making the entry into the worksheet i need to tell the process what to do next. Such as Quit, get another input, or print the sheet.

    I open a second form with buttons to make those choices. It appears as desired, but it will not accept any action to form2. I suspect that it is because the focus has been moved over to the sheet rather than the form but I can not find the proper method to make the form work.

    Can anybody advise me?
    Win 8, Firefox 16.0.2, Thunderbird 15.0.2

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wont accept Input (Excel 2000)

    Have you closed the first form?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    East Tennessee
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Wont accept Input (Excel 2000)

    No! I haven't closed the form1. I did hide it.
    I tried deactivate,and several others that came back with debug errors, including Close.Formname and formname.close. The help files seem to indicate that close is used to end output to a file.

    Completely mystified! But Thanks.
    Win 8, Firefox 16.0.2, Thunderbird 15.0.2

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Wont accept Input (Excel 2000)

    To paraphrase the fellas down at "Dockside Dolls" gentlemens club, "SHOW US YER CODE!" <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    You probably have code in both the subroutine that calls the UserForm and in the UserForm itself. Maybe someone will see the problem if you post that code here. When you get a "debug" error the important clue is what line of code it chokes on (highlights) when you click Debug.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wont accept Input (Excel 2000)

    It is either:

    Userform1.Hide

    which hides the form, but keeps it in memory or

    Unload Userform1

    which hides it and removes it from memory.

    If you need to get data out of the form, you first hide it, get the data into variables and then unload it. If you need to reshow the form with the same data and selections, you don't unload it, unless you plan to repopulate the form in code.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    East Tennessee
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Wont accept Input (Excel 2000)

    Thanks for your response. My problem was that I had set the the second form(MoreForm) to "Enabled = False"
    Which I was able to fix buy setting the Enabled property to true.

    Now however, I have more strange behavior.
    I can move from #1 (SalesForm) to #2 (MoreForm) and from there back to #1 but, when I attempt to go back to #2 a second time the VB Quits. Code for the buttons on the two forms is below;

    Private Sub DoneBut_Click() 'On SalesForm
    sdate = datebox.Text
    Worksheets(sname).Select
    Range(Colname).Select
    Selection.End(xlDown).Offset(1, 0).Select
    Selection.Value = sdate
    Selection.Offset(0, 1).Value = sale
    Selection.Offset(0, 2).Value = stax
    SalesForm.Hide
    MoreForm.Enabled = True
    MoreForm.Show
    End Sub

    Private Sub AgainBut_Click() 'On MoreForm
    SalesForm.Enabled = True 'probably not required
    SalesForm.Show
    MoreForm.Hide
    End Sub
    Win 8, Firefox 16.0.2, Thunderbird 15.0.2

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wont accept Input (Excel 2000)

    I think things will work better if you call the forms like this:

    Private Sub DoneBut_Click() 'On SalesForm
    sdate = datebox.Text
    with Worksheets(sname).Range(Colname).End(xlDown)
    .Offset(1, 0).Value = sdate
    .Offset(0, 1).Value = sale
    .Offset(0, 2).Value = stax
    end With
    Me.Hide
    MoreForm.Enabled = True
    MoreForm.Show
    Me.Show
    End Sub

    Private Sub AgainBut_Click() 'On MoreForm
    SalesForm.Enabled = True 'probably not required
    MoreForm.Hide
    End Sub

    Note I did three things:

    1. I removed the selecting in the first code.
    2. I added Me.Show to the first one after the MoreForm.Show
    3. I removed the SalesFrom.Show from the second one.

    Since the Me.Show in the first sub will be executed once Moreform is closed (hidden), the Show method in the second sub is obsolete.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    East Tennessee
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Wont accept Input (Excel 2000)

    Thanks for your input. It still did not work the way I wanted, so I constructed a test with two forms as shown below. That test will allow me to move back and forth between the the forms as much as I like. showing one or the other. Whereas the original set still shuts down when I attempt to show MoreForm the second time.

    Private Sub Button1_Click()
    Win 8, Firefox 16.0.2, Thunderbird 15.0.2

  9. #9
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    East Tennessee
    Posts
    179
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Wont accept Input (Excel 2000)

    After I sent the last post, I was experimenting some more.

    I discovered that if on the MoreForm, the MoreForm.hide is before the SalesForm.Show then the "Shutdown problem" is eliminated.
    Win 8, Firefox 16.0.2, Thunderbird 15.0.2

Posting Permissions

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