Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-17-2018, 09:21 AM   #16
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,144
Thanks: 349
Thanked 533 Times in 515 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

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.

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


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

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
smiler44 is offline   Reply With Quote
Old 10-17-2018, 10:59 AM   #18
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,144
Thanks: 349
Thanked 533 Times in 515 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

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

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


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 10-17-2018, 11:09 AM   #19
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,144
Thanks: 349
Thanked 533 Times in 515 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: add value of variable to value in text box

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


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

Gasman, can you tell me more about this?
"There are actually buttons on the ribbon to do blocks of code."

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

Make sure you have the Edit toolbar selected in the Code window. Right click blank part of toolbar.

You should then see the icons in the attached pic.
Attached Images
File Type: png indent.PNG (864 Bytes, 24 views)
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 10-18-2018, 12:09 PM   #22
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 617
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
Make sure you have the Edit toolbar selected in the Code window. Right click blank part of toolbar.

You should then see the icons in the attached pic.
oh ya.
cheers

smiler44


smiler44 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 08:34 AM.


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