Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    USERFORM... (2000 sr 1)

    I have this code:

    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 300
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime

    next part of code


    during this time delay instruction i want would appear a user form "attendere prego", when the time attending is finished unload userform and go to nex part of code...

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

    Re: USERFORM... (2000 sr 1)

    Create a userform with one large label on it.
    Name the userform frmWait, and set the Caption property of the label to "Attendere Prego".

    To display the form for a period of time, use

    frmWait.Show 0
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 300
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Unload frmWait
    ...

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    ... simple and effective

    only with you step by step i understand every thing...


    comment: option explicit
    when you or other appreciate user send me an example, IN EVERY SIDE, of scriptS is present this option but i dont insert i my work and it work normaly, is important to insert in the head of code this.?

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

    Re: USERFORM... (2000 sr 1)

    Sal,

    I recommend using "Option Explicit". You can turn it on for new modules as follows:
    - Activate the Visual Basic Editor.
    - Select Tools | Options...
    - In the Editor tab, tick the check box "Require Variable Declaration".

    When you have Option Explicit in a module, you MUST declare every variable you use, for example

    Dim newHour As Long
    Dim newMinute As Long
    Dim newHour As Long
    Dim waitTime As Long

    newHour = ...

    If you do not have Option Explicit in a module, you can use variables without declaring them. The code will work normally, but a small typing error can have unexpected effects. Take this example, taken from your post in this thread:

    Dim newHour As Long
    Dim newMinute As Long
    Dim newHour As Long
    Dim waitTime As Long

    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 300
    waitTime = TimeSerial(newHour, newMinute, newSeconds)
    Application.Wait waitTime

    In the instruction that sets waitTime, there is an error: newSeconds is used instead of newSecond. If you have Option Explicit, you'll get an error message when you try to run the code: "Compile Error, Variable not defined", and newSeconds will be highlighted. If you don't have Option Explicit, VBA will automatically create a new variable newSeconds, with value 0. So waitTime will be wrong, it will be a time that is already past!

    Turning on Require Variable Declaration will only insert Option Explicit in new modules, not in existing ones. You can insert Option Explicit manually at the top of existing modules. This may lead to lots of error messages "Variable not defined" in the beginning, and this can be annoying. But in the long term, it will prevent unexpected errors.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    Mmythics!!!!

    You remember my old note: ancient Greece people...

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    In addition to what Hans has said, calculating waitTime this way can produce some very unpredictable results. The time continues to run while that code is executing. If the time were to change from lets say 12:59:59 to 1:00:00 between the newHour = Hour(Now()) and newSecond = Second(Now()) + 300 you are not going to end up waiting for the expected amount of time. It would be much better to calculate waitTime like this:

    <pre> waitTime = Now()+300/86400
    </pre>

    Legare Coleman

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    i dont understand completly:-(
    i use only this line to attending a time

    or use this line in adding to this

    frmWait.Show 0
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 300
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Unload frmWait
    ...

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    The whole thing would look like this:

    <pre>Dim waitTime As Date
    frmWait.Show 0
    waitTime = Now() + 300 / 86400
    Application.Wait waitTime
    Unload frmWait
    </pre>

    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    always for this project...
    I have make this modify to speed the SEARCH, but when i select fron the combobox1 and select one in the txtbox appear the number selected in BLUE....
    i dont like this (is not completly visible)...
    see jpg

    Private Sub ComboBox1_Change()
    TextBox1.Text = ComboBox1.Text
    Call CommandButton1_Click
    End Sub

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    ok! tks.

    ... but this instruction define 60''*5 =300 (5 minuts)

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

    Re: USERFORM... (2000 sr 1)

    If the text in the combo box is not completely visible, set its TextAlign property to 1 - frmTextAlignLeft (as recommended before.)

    You can add a line

    ComboBox1.SelLength = 0

    to the code, but I don't get consistent results. Sometimes it works, sometimes it doesn't.

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    My dear rememeber you the problem refered "blank line in the list box..., i hope)

    to correct the problem of alling to right i have modify your old instruction:

    Sheets("L0785_PAGATI").Select
    ' Check if there are records
    If Range("Q7") = "" Then
    ' No - leave combo box blank
    Indirizzario.ComboBox1.RowSource = ""
    Else
    ' Yes, set row source
    Indirizzario.ComboBox1.RowSource = Range(Range("Q7"), _
    Range("Q65536").End(xlUp)).Address(External:=True)

    symply in:

    .......
    ' Yes, set row source
    Indirizzario.ComboBox1.RowSource = ("Q7:R65000")
    .........
    and with this new instruction i see the blank line......

    is possible to modify this line without change the new range?

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

    Re: USERFORM... (2000 sr 1)

    Try this:

    Indirizzario.ComboBox1.RowSource = Range(Range("Q7"), _
    Range("Q65536").End(xlUp)).Resize(, 2).Address(External:=True)

    This takes the cells in column Q that are filled, and resizes the range to include 2 columns instead of 1.

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    MEGA GALACTIC CODE!!!!

    ... are you an angel?

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: USERFORM... (2000 sr 1)

    In my code 300/86400 is 300 seconds converted to an Excel time value. 86400 is the number of seconds in a day. Dividing 300 by 86400 will give you 300 seconds (5 minutes) in Excel time units. You could also use 5/1440 which is 5 minutes divided by the number of minutes in a day.
    Legare Coleman

Posting Permissions

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