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
MyPath = "R:\COMMON\ORACLE\TEST_UPDATE FORMS"
ChDrive MyPath
ChDir MyPath
Attachment1 = GetAttach
attach.Text = Attachment1
ChDrive SaveDriveDir
ChDir SaveDriveDir
End Sub#
thats great thankyou,
do you know if it is possible to say have a series of numbers in a cell(a1)and then when you push save, it would then save it as the values in a1?
another question... is it possible to save something as the time and date of the save?
thanks for your help
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
ActiveSheet.copy
ActiveWorkbook.SaveAs Filename:="C:\SCH\1.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
'
End Sub
tried this and it returned nothing
my code
Worksheets("Sheet1").Range("b13").Value = ComboBox1.Value
last question promise!!
any if it was a combobox!!!
thank you very much
excellent,
does anyone know what i would put if it was a label i.e Worksheets("Sheet1").Range("c2").Value = textbox1.text what would a label be if it was name h, i tried h.label but no success??
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,
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0))))
,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
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
Steve
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.
=IF(WEEKDAY(D90)<WEEKDAY(A90),(D90+E90)-(A90+B90)-2,(D90+E90)-(A90+B90))
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
steve
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
steve
Hi all,
Really would appreciate someones help, here cos im really lost.
I have two timings, one start (column a and B) and finish (D and E).
What i want to do is work out the amount of time it took but not counting the weekend period. Any suggestions?
Cheers
Steve
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
Steve
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!!!!!!!
Cheers
Steve
hi roy i tried this and now jo9y.
cant work this out because i have literally deleted the sheet data (valiudation lists, vb code, date) and it is still 1.4mb!
any ideas?
