add value of variable to value in text box (1 Viewer)

smiler44

Registered User.
Local time
Today, 18:49
Joined
Jul 15, 2008
Messages
641
I want to add the run time of a song from a variable to the time already in a text box and then have the text box display the new time.

The format is 00:00:00

when the next song is played I want to add this time to the time in the text box as well and show the new time and so on and so on.

I thought tis was easy but I'm beaten and what I've fond on the web has not worked for me.


example:-
textbox1.value = textbox1.value + b

smiler44
 

isladogs

MVP / VIP
Local time
Today, 18:49
Joined
Jan 14, 2017
Messages
18,186
Something like the attached?
 

Attachments

  • RunningTime.PNG
    RunningTime.PNG
    3.6 KB · Views: 104
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 18:49
Joined
Sep 21, 2011
Messages
14,046
I would have gone with =A2 in cell B1 as Colin has
Then a formula of =Sum($A$1:A2) in cell B2, then copy down as needed for the rest of column B.
You will need to format the columns for hh:mm:ss if you expect to put over an hours worth of music before you go to sleep.
 

Attachments

  • smiler.PNG
    smiler.PNG
    8.4 KB · Views: 103

smiler44

Registered User.
Local time
Today, 18:49
Joined
Jul 15, 2008
Messages
641
for now I've come up with the same method as isladogs. I then populate my tet box from the cell value, this works but I'm not happy with it.

I was hoping to populate the variable and then simply go
textbox2.value = textbox2.value + variable but I cant get this to work.

1, cant get the add code right
2, something to do with time perhaps but I can see there is some extremely long number. I don't know if the minutes and seconds are being "converted" into something. something like 38E004

main thing, I can now randomly select songs to play for a certain length of time or to the end of the song that's being played and then shut down windows media player and my lap top.

just want Excel to look better so avoiding filling 3 cells would be ideal.


smiler44
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:49
Joined
Sep 21, 2011
Messages
14,046
Well if you put the formula in column B for 100 rows, then all you would need to do is put the values in column A ?
If you wanted to be fancy you would add the name of the track in another column?

Are you trying to do this all in one row?
 

smiler44

Registered User.
Local time
Today, 18:49
Joined
Jul 15, 2008
Messages
641
Well if you put the formula in column B for 100 rows, then all you would need to do is put the values in column A ?
If you wanted to be fancy you would add the name of the track in another column?

Are you trying to do this all in one row?


i currently am using cells A1, A2 and A3, at worst i only want to use A1 just to put the run time of the current song into.
A1 run time of current song
A2 = text2 value which is run time of songs played so far
A3 = A1 + A2
text2.value = A3 value



i want something along the lines of
b= runtime of current song
text2.value = text2.value + b

smiler44
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:49
Joined
Sep 21, 2011
Messages
14,046
Don't think you can do that in Excel just with formulae, as you re creating circular references.?
If you set text2 to A3, that becomes A2 which becomes A3 and so on.
Do the math in vba and then populate the cells.

Then you can do something like
Code:
TotalTime = TotalTime + RunTime
 

smiler44

Registered User.
Local time
Today, 18:49
Joined
Jul 15, 2008
Messages
641
Don't think you can do that in Excel just with formulae, as you re creating circular references.?
If you set text2 to A3, that becomes A2 which becomes A3 and so on.
Do the math in vba and then populate the cells.

Then you can do something like
Code:
TotalTime = TotalTime + RunTime

not using formula but a macro but text2.value = text2.value + b
is not right because if textbox2 contains 3 and b= 5 this code gives 35 instead of 8. next time it runs it gives 355.

I am lost on how to add the values of two text boxes together and am lost on how to add 2 times together. both I thought were easy

smiler44
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:49
Joined
Sep 21, 2011
Messages
14,046
That would lead me to think that text2.value is a string, or at least being treated as a string?
 

smiler44

Registered User.
Local time
Today, 18:49
Joined
Jul 15, 2008
Messages
641
How else do you account for the fact that it appears to concatenate the values and not sum them then.?

sorry i dont know. b is a variable but perhaps incorrectly i have just gone dim b
and not declared it as string or integer, this could be the problem, what do you think?

smiler44
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:49
Joined
Sep 21, 2011
Messages
14,046
Why not show the whole code or load the excel workbook.?
If showing the code, please put within code tags.

There is little point trying to drag bits of information from you piecemeal.

What I can tell you is
1. That any format function makes the result a string.
2. You can concatenate values using the + character as well as the & character
 

smiler44

Registered User.
Local time
Today, 18:49
Joined
Jul 15, 2008
Messages
641
what I have been trying to do is play random songs for a set length of time then shut down windows media player, excel and my laptop.
with help from here, the internet and past projects I have a macro that works, perhaps not bug free but working.

if I input 90, macro stops at anything over 1 hour so I must set duration to play as 1:30.
no idea what happens if the total play time of songs is less then 90 minutes.
I forget to tick the check box to shut laptop down so laptop stays fired up after music stops.:banghead:
There are other ways perhaps even better ways to have sorted out playing for a set time but then I would not have this code or know how to get file properties



Here is the code and thank you for the help, smiler44

Code:
Private Sub Workbook_Open()
Sheet1.TextBox1.Value = "00:90:00"
End Sub
this command button is on sheet1
Code:
Private Sub Cmd1_Click()
Call start
End Sub
Code:
Dim fd 'file duration of song
Dim filetoplay ' the mp3 file to play
Global spt As String

Sub start()
' play music for pre determined time
Sheet1.Range("A1").Value = "00:00:00"
Sheet1.Range("A2").Value = "00:00:00"
Sheet1.TextBox2.Value = "00:00:00"
Call randomlyselectsong
If Sheet1.CheckBox1.Value = True Then ' shutdown laptop
Call shutdown 'shut down laptop
End If
'MsgBox ("finished")
End Sub
 
 
Sub shutdown()
Shell ("taskkill /f /im wmplayer.exe") 'shutdown windows media player
Shell "shutdown -s -t 05", vbHide 'shut down laptop
Application.Quit
End Sub
Code:
Dim fs, f1, fc, s
Dim ftp
Dim b 'amount of time to pause for while song plays
'other wise next song play immediately

Public Sub randomlyselectsong()
Dim mm ' minutes
Dim ss ' seconds
Do Until Sheet1.TextBox2.Value > Sheet1.TextBox1.Value
Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.GetFolder("path to folder where songs are stored")
Randomize
i = CInt((Rnd() * f.Files.Count) + 1)
j = 1
For Each fi In f.Files
If j = i Then
ftp = fi.Name 'fi, randomly selected song
Call RecursiveDir(Directory)
filetoplay = """path to folder where songs are stored\" & ftp
Shell "C:\path\wmplayer /play /close " & filetoplay
''''''''''''''''''''''''''''''''''''''''
   'Sheet1.TextBox2.Value = Range("a3").Text
   Range("a2").Value = Sheet1.TextBox2.Value
   Sheet1.TextBox2.Value = Range("a3").Text
ss = Right(b, 2) ' gets far right 2 charectors
mm = Mid(b, 4, 2) 'starting at 4 from left, gets the 4th and 5th charector
b = mm * 60 + ss
          
pause (b) ' how long to wait in seconds before playing next song
'''''''''''''''''''''''''''''''''''''''''
End If
j = j + 1
Next
Loop
End Sub
Public Sub RecursiveDir(ByVal currdir As String)
  Range("A1").Select
  Range("a1").Value = FileInfo("path to folder where music is stored\", ftp, 27)
             
End Sub
Function FileInfo(path, filename, item) As Variant
' this gets the file duration
    Dim objShell As IShellDispatch4
    Dim objFolder As Folder3
    Dim objFolderItem As FolderItem2
      
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objShell.Namespace(path)
    Set objFolderItem = objFolder.ParseName(filename)
    
    FileInfo = objFolder.GetDetailsOf(objFolderItem, 27)
    b = FileInfo ' song run time. used to pause macro while song plays
           
    Set objShell = Nothing
    Set objFolder = Nothing
    Set objFolderItem = Nothing
End Function

Sub pause(seconds As Single)
  Dim TimeEnd As Long
           
           TimeEnd = Timer + seconds
        
            If TimeEnd > 86390 Then
                TimeEnd = 0
            End If
                
            Do
                DoEvents
            Loop Until TimeEnd <= Timer
        
        End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:49
Joined
Sep 21, 2011
Messages
14,046
Time is held as a fraction of a day.
If you go onto the debug window and set A1 to 0.5 and the cell is formatted as hh:mm:ss you will see 12:00:00.

So if you format the cell for hh:mm:ss then you need to add the values as seconds, then divide by the number of seconds in a day (86400) to get the correct value for that time.

So keep everything in seconds and then do the math to get the decimal of a day which in turn will show what you are looking for I believe.

You are already doing that for b, but trying to add to a formatted value.
If you enter a time in cell A1 formatted as hh:mm:ss and then show the value in the debug window with
Code:
? Range("A1").Value
it will show the true value, which is a fraction representing that part of the day.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:49
Joined
Sep 21, 2011
Messages
14,046
First observations.

Dim your variables with the correct type.
You have
Code:
Set fs = CreateObject("scripting.filesystemobject")
within your loop. That should be created once I would have thought?, and reused within the loop.
Use the Macro recorder to see what code you need for various tasks.
To format the cell as hh:mm:ss you would use
Code:
Selection.NumberFormat = "hh:mm:ss"
but you would put the value 0 in the cell.
I would have thought you would be using the same sheet for this, so you could set the formats manually and not repeat them every time the code runs.?
Indent your code so you (and anyone else) can easily see the structure of it.
 

smiler44

Registered User.
Local time
Today, 18:49
Joined
Jul 15, 2008
Messages
641
Gasman, thank you. I thought my problem was to do with time but I was was off beam. I'm a bit wiser now

As for indenting, I struggle with it, always have.

the Set fs = CreateObject("scripting.filesystemobject") is someone elses code and so I would not know what to do with it.

I'm lost on how to make things call a function, just dont know how it all works and to be honest the line of code above has me lost.

I'm only using sheet1 but have 2 modules for the code plus code that sits with sheet 1.

smiler44
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:49
Joined
Sep 21, 2011
Messages
14,046
I believe if you put that line outside of your loop it would work just as well. I'd certainly try it.

You already are calculating b in seconds, so keep the units as seconds.
I've not created any Excel forms, but can see that the .Text value can be 00:00:00, so you will need to calculate the seconds involved as you do for b.

Only then can you test the total against that required.
Use type long for your seconds.

So is you want 90 minutes, the value you would put in your textbox would be 01:30:00

Personally I would do it all in Excel cells and forget about any userform?

HTH
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:49
Joined
Sep 21, 2011
Messages
14,046
Indenting
Really helps when checking correct loops, ifs etc
Also use space to easily see sections of code
Use the tab key to indent. There are actually buttons on the ribbon to do blocks of code.

Code:
Public Sub randomlyselectsong()
Dim mm ' minutes
Dim ss ' seconds

Set fs = CreateObject("scripting.filesystemobject")
Set f = fs.GetFolder("path to folder where songs are stored")

Do Until Sheet1.TextBox2.Value > Sheet1.TextBox1.Value

    Randomize
    i = CInt((Rnd() * f.Files.Count) + 1)
    j = 1
    For Each fi In f.Files
        If j = i Then
            ftp = fi.Name 'fi, randomly selected song
            Call RecursiveDir(Directory)
            filetoplay = """path to folder where songs are stored\" & ftp
            Shell "C:\path\wmplayer /play /close " & filetoplay
            ''''''''''''''''''''''''''''''''''''''''
            'Sheet1.TextBox2.Value = Range("a3").Text
            Range("a2").Value = Sheet1.TextBox2.Value
            Sheet1.TextBox2.Value = Range("a3").Text
            ss = Right(b, 2) ' gets far right 2 charectors
            mm = Mid(b, 4, 2) 'starting at 4 from left, gets the 4th and 5th charector
            b = mm * 60 + ss
          
            pause (b) ' how long to wait in seconds before playing next song
            '''''''''''''''''''''''''''''''''''''''''
        End If
        j = j + 1
    Next
Loop

End Sub
 

smiler44

Registered User.
Local time
Today, 18:49
Joined
Jul 15, 2008
Messages
641
Gasman, can you tell me more about this?
"There are actually buttons on the ribbon to do blocks of code."

smiler44
 

Users who are viewing this thread

Top Bottom