Posts by stevehorton09

    Hi Guy/gals,

    Is it possible to find a worksheet in a specific location?

    I.e I have a location on the c drive where all my files are kept and are saved as numbers i.e 1111, 2222 etc etc

    Is it possible for a macro/ vb statement to go and find that file and then open it (making it the active worksheet).

    I expect this is very simple but i have never had to use the find function (*) before.

    Hi there,

    I want to be able to move a file through the use of a command button in vb.

    The idea would be that after a file was approved it would be moved to the approved folder

    The form would have a text box with the file location in, below this ,there would be two command buttons, one approve and one reject.

    Therefore the file which is waiting to be accepted has the location C:\sch and when a comand button (approve) is pressed it moves to C:\sch1, if though it is rejected then it goes to c:\sch2

    Is this possible??

    Hi There,

    My current vb form allows the user to choose an attachment and then the mail through the form. Im using lotus notes.

    Is it possible that the current worksheet could be the attachment.

    I.e it attaches current worksheet automatically.

    I can get the code to search in a specific directory but how can i make it automtically just select the current worksheet?

    My code:

    #Private Sub CommandButton3_Click()

    ' setting and attaching the work book
    Dim MyPath As String
    Dim SaveDriveDir As String
    Dim saveasname As String

    SaveDriveDir = CurDir

    ChDrive MyPath
    ChDir MyPath

    Attachment1 = GetAttach
    attach.Text = Attachment1

    ChDrive SaveDriveDir
    ChDir SaveDriveDir

    End Sub#

    Hi there,

    Currently i have a vb statement that will copy a sheet and then save it to a certain location.

    what im interested to do is to set it up so when i click the button the first is called 1, then 2 etc.

    Also is it possible to save them as the current date and time??

    my current code is below.

    Sub savings()

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\SCH\1.xls", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _

    End Sub

    Hi there,

    I have created a user form in vba that someone would fill in various text boxes.

    What i now want is the info to be summarised in excel.

    The userform is held in sheet 1, so the idea is the user would input the information in the text boxes and then click the command button and the text boxes information would be copied to indiviual cells i.e textbox1 to a1 on sheet 1.

    I have tried by running a macro in excel to see the vb code but that does not work for a vba form.

    If anyone needs the file let me know and i will attach it

    Hi all,

    My current code allows someone to to click on the combo box and enter the first few letters of a word and if it is in the combo box it is picks up.

    How can i change the code so it does this and also when the arrow button is pushed a list appears??

    my current code

    rivate Sub ComboBox1_Change()
    ComboBox1.AddItem "New Entity"
    ComboBox1.AddItem "New Location"
    ComboBox1.AddItem "New Activity"
    ComboBox1.AddItem "New Product"
    ComboBox1.AddItem "New Business"
    ComboBox1.AddItem "New Project"
    ComboBox1.AddItem "Description Change - Entity"
    ComboBox1.AddItem "Description Change - Location"
    ComboBox1.AddItem "Description Change - Activity"
    ComboBox1.AddItem "Description Change - Product"ComboBox1.AddItem "Close Entity"
    ComboBox1.AddItem "Close Location"
    ComboBox1.AddItem "Close Activity"
    ComboBox1.AddItem "Close Product"
    ComboBox1.AddItem "Close Business"
    ComboBox1.AddItem "Close Project"
    ComboBox1.AddItem "New Cross Validation Rule"
    ComboBox1.AddItem "New Alias"
    ComboBox1.AddItem "Description Change - Business"
    ComboBox1.AddItem "Description Change - Project"
    ComboBox1.AddItem "Close whole cost centre"

    ComboBox1.Style = fmStyleDropDownCombo
    End Sub

    Timings issue

    Hi, I have the following code,


    This is fine for one line. i.e

    09/07/2004 10:00 12/07/2004 10:00 1 days 0 hours

    How though do i modify this formula so it will work out multiple lines i.e

    15/07/2004 10:00 18/07/2004 11:05
    16/07/2004 10:00 19/07/2004 11:05
    17/07/2004 10:00 20/07/2004 11:05
    18/07/2004 10:00 21/07/2004 10:00
    19/07/2004 10:08 22/07/2004 10:25

    I have also attached my spreadsheet

    Thanks and regards


    Thanks for the formula rich,

    I have use this but it seems to ignore when there is a larger gap of days i.e 9th to the 13th. I enclose below some of my input
    Time between
    09 July 2004 09:40:41 0:36:11 13 July 2004 10:16:52

    The formula used is as follows as the date and time are seperated.


    It seems to also fall over for this situation
    Time between
    09 July 200407:25:01 3:44:12 12 July 2004 11:09:13

    I have also attached the example

    thanks for your help guys

    Hi Ray,

    I have had a go but cant seem to get it to recognise the startdt, is this vb coded?

    If you do have time that would be excellent as this is over my head!

    Do you have an example of this working (i.e pearsons approach)

    Hi Ray,

    I have added this add on so no worries there. the data does actually come to me with the time and date together.

    i enclose the original form.

    08-Jul-04 10:39:27 09-Jul-04 08:31:30
    08-Jul-04 10:41:42 09-Jul-04 06:16:42
    08-Jul-04 10:50:51 12-Jul-04 03:42:04
    08-Jul-04 10:52:19 09-Jul-04 04:46:18
    09-Jul-04 09:28:08 12-Jul-04 09:25:54

    Thanks in advance


    Hi below is what happened with the formula. am i missing something?

    What i was hoping for was that the weekend days would be missed out and the rest of the time added up.

    E.g for the first line the rest of the 8th, all of 9th and then 9hrs and 4 mins for the 12th should be added together

    Submitted Approved
    08 July 2004 4:51:13 AM 00:00:00 12 July 2004 9:04:50 AM
    08 July 2004 5:12:47 AM 00:00:00 09 July 2004 6:53:33 AM
    08 July 2004 8:31:48 AM 04:13:37 12 July 2004 6:36:58 AM
    09 July 2004 9:54:24 AM 01:40:46 12 July 2004 4:06:18 AM
    07 July 2004 3:50:01 AM 22:05:10 08 July 2004 6:15:15 AM

    thanks for your help


    Hi all,

    As attached i have a spreadsheet that works out the percentage of particular tasks per country, per month.

    The spreadsheet shows one month and along the columns it is split by countries.

    What i need is that the total percentages for all countries should be added up and formulated to show for example Uk activities took up 60% of the work month etc.

    I have got a feeling Im lost in the world of formulas!

    Hi everyone,

    I have a summary page which has the amount of activities done, average time time taken and total time taken. I want the next column to be a percentage of the time it took up in the month.

    I.e if we did 872 sys admin tasks which take ten minutes each then that is 145 hours work and of a working month (160 hrs) 90% approx.

    I can not seem to get this formula right though!!

    Can anyone help, i have enclosed the worksheet

    Thanks and regards


    Hi All,

    I have ten workbooks (one tab on each) and need a macro that can put all the data into a single work book.

    The names of the workbooks are 1,2,3 etc to 10 and the headings are all the same, so i need to copy the data into a master worksheet/book (called master). The data should not write over eachother!!!

    Really desperate for help!!!!!!!