Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-14-2018, 01:12 AM   #1
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
add value of variable to value in text box

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

smiler44 is offline   Reply With Quote
Old 10-14-2018, 02:17 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,059
Thanks: 110
Thanked 2,728 Times in 2,491 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: add value of variable to value in text box

Something like the attached?
Attached Images
File Type: png RunningTime.PNG (3.6 KB, 25 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 10-18-2018 at 11:17 AM.
isladogs is offline   Reply With Quote
Old 10-14-2018, 02:39 AM   #3
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,964
Thanks: 415
Thanked 713 Times in 692 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

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.
Attached Images
File Type: png smiler.PNG (8.4 KB, 20 views)

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 10-14-2018, 03:42 AM   #4
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: add value of variable to value in text box

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
smiler44 is offline   Reply With Quote
Old 10-14-2018, 03:48 AM   #5
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,964
Thanks: 415
Thanked 713 Times in 692 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

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?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 10-14-2018, 05:04 AM   #6
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: add value of variable to value in text box

Quote:
Originally Posted by Gasman View Post
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
smiler44 is offline   Reply With Quote
Old 10-14-2018, 05:40 AM   #7
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,964
Thanks: 415
Thanked 713 Times in 692 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

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

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 10-14-2018, 05:55 AM   #8
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: add value of variable to value in text box

Quote:
Originally Posted by Gasman View Post
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
smiler44 is offline   Reply With Quote
Old 10-14-2018, 05:58 AM   #9
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,964
Thanks: 415
Thanked 713 Times in 692 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

That would lead me to think that text2.value is a string, or at least being treated as a string?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 10-14-2018, 10:38 AM   #10
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: add value of variable to value in text box

I don't think it is

smiler44
smiler44 is offline   Reply With Quote
Old 10-14-2018, 11:18 AM   #11
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,964
Thanks: 415
Thanked 713 Times in 692 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

How else do you account for the fact that it appears to concatenate the values and not sum them then.?

Quote:
Originally Posted by smiler44 View Post
I don't think it is

smiler44
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 10-14-2018, 11:50 AM   #12
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: add value of variable to value in text box

Quote:
Originally Posted by Gasman View Post
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
smiler44 is offline   Reply With Quote
Old 10-14-2018, 01:32 PM   #13
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,964
Thanks: 415
Thanked 713 Times in 692 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 10-17-2018, 05:45 AM   #14
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: add value of variable to value in text box

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.
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
smiler44 is offline   Reply With Quote
Old 10-17-2018, 09:06 AM   #15
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,964
Thanks: 415
Thanked 713 Times in 692 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

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.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Text Box Variable hascons Modules & VBA 3 05-15-2010 06:09 PM
Text/variable formatting? mafhobb Modules & VBA 4 05-21-2009 12:11 PM
text box to reference a variable name Geordie2008 Reports 2 04-08-2008 04:26 AM
Default value = text + variable dirk Forms 3 01-24-2007 06:51 AM
Variable as a text box name Uncle Gizmo Modules & VBA 6 09-28-2003 10:05 AM




All times are GMT -8. The time now is 02:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World