I have a file which ask for UserId and password. If password is correct, the time stamp should be recorded on the worksheet. However I have some slight probelms on the code I am working on. can someone help me check my coding?
View 14 Replies
I have made an excel file the use of which I want to restrict to a few people. So I want that whenever the user will open the file it will prompt for user id and password. If these matches then he will be allowed to access the file. Thre types of user id and password is required:
User! : Can modify, edit, delete, etc ie full access (Adminstrator type)
User2: Only access to print (no entry,edit,modifiaction,etc)
User3: Only be able to see the file and no print,entry,etc access.
If all are not possible, then provide code for part.
Macro will always be enabled as I found code on the net to force user to enable macro. ie if macros are not enabled, it shows another rough page. So enabling macro is not a problem.
I have a row of cells, and some of which are displaying time, but in an incorrect format.
for example 15:20 is showing in a cell as 3.2
How can I create a new cells that basically converts 3.2 into 24 hr hh:mm ....e.g. 15:20?
If I got to use the format cells option, and hh:mm, it changes the cell to the completely wrong time (i.e 04:48) ?
p.s. I should state that "some" of the cells are kind of showing the correct time, albeit with a decimal instead of a colon, for example, cell A12 is showing 4.25
one other thing I should mention.....the times will always be PM...... so 3.2 should be 15:20, and not 03:20
How can I correct this Time formula which says in the Tag that it produces a
negative Result and hence displays in cell as ####
=(INDEX($A18:$A33,MATCH(MIN(C18:C33),C18:C33,0)))-(60/1440)
What I am doing is selecting the field in A which equates to the lowest
value in C (C= hourly sales), then I am subtracting 60 mins from the value
selected in A. This in effect will give me my closing Times i.e. find the
cell with Zero Sales then subtract 1 hour to find what must be the closing
time (assuming of course that there is at least ?1 of sales per hour while
open).
My cells in A are formatted as h:mm AM/PM, as is the format in the formula
cell
Is there a way to log the userid of the person who last made an update in the external file links?
View 4 Replies View RelatedI have done a VBA course but am struggling to apply it to my situation. What I want to do is on one spreadsheet, I have a column with "shift, date, grade 1, grade 3, quarantine" in it, and in the cells next to it I want an operator to enter either "day" or "night" next to shift, and the date. Next to the others I want the total weight of the appropriate product made in that time. The spreadsheet I will be feeding off is quite big with a lot of information in it. Down the left hand side is the roll number (we make rollstock). Further along on that same row will be the date, the weight, and a grading (putting it into grade 1, 3, quarantine etc). Our shifts run from 7am to 6:59pm, and 7pm to 6:59am. Which shift the roll comes under is determined by it's end time (also in that row). So basically, I need VBA to say sum up all of column [weights] if column [date] = [date cell in working spreadsheet] and if working spreadsheet says day then column [roll end time] =7:00-18:59 and if working spreadsheet says night then column [roll end time] =19:00-6:59 and also if column [grade] = [grade cell in working spreadsheet] ....
Oh, also on the feed spreadsheet i have a column that i put in a little code date, "AP04" for 4th of april kind of thing, rolls being made before 6:59am on the 5th of april being put into the 5th april as they need to be counted under 4th april as they are 4th april night shift (even though the date will stay 5th april)...
B. start time C. end time D. Total time (military time)
Example: 2300 - 0300 = 4 hrs worked.
=IF(C19-B19>0,C19-B19,1+C19-B19)
I have a row for every date the hors is worked.
I cant seem to get the total hours to calculate correctly at the bottom of column D for a total hours worked in a particular pay period. It calculates a total up to 24hrs then reverts to zero. I have tried [h]:mm =SUM(XX:XX) and just about everything else I can think of and cant get it to calculate a correct total over 24 hrs.
Column A = persons name
Column B = number of hours
Column C = trade
Column D = shift (as in day, night or weekend)
Column E = I would like the formula that looks at the trade and time and then puts the correct rate.
I have a little table on the site with trades/times and rates. IF and IF THEN do something
I took a screenshot to show: [URL] ..........
I need a formula that will look up a cell to get a figure from, but there is three of the same name (sometimes more, depending on different products sold) i.e. "Dept Total" (shown below & attached for easier reading) ....
View 7 Replies View RelatedI have one work sheet (worksheet #1) that everyone in the company uses. With this worksheet they submit orders to my department.
When I receive it (worksheet #1) I have a macro built in so that all i have to do is click a button and the sheet get's logged onto a certain workbook (workbook #1) on the appropriated tabbed sheet.
On my log workbook (workbook #1) I have two tabed sheets "2005" and "2007", and all of the worksheet#1's go to the next line on "2007"
On occasion a sales person will open up an old worksheet and the macro will log it onto "2005".
I want to create a macro for (workbook #1) that goes something like this:
If any files attempt to write to sheet "2005"
do not allow or (False)
move line item to sheet "2007" to the next open line.
need to enter data of various patients in each row.each patient variables being entered in different rows.but problem is certain reports have multiple values pertaining to different dates but they are of the same patient. how is it possible to enter different values but for the same individual??
View 9 Replies View RelatedI'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies View RelatedI have a form on which I select a date (using a calendar form) and on the form it works perfectly. The date is in the correct dd/mm/yyyy format for example (03/04/2013 - 3rd April 2013), I then insert this date into a worksheet and it comes out in the correct format, but it is actually turning the dates around so where it should be putting 3rd of April (03/04/2013), it's inserting 4th March (04/03/2013) instead. It looks very much like the form sees the date in dd/mm/yyyy format and the worksheet when taking the data from the form thinks it in US format (mm/dd/yyyy).
[Code] .....
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
I'm using MS Excel 2003 and have a significant amount of comments on my sheet. I've noticed that they are beginning to disappear which is a big problem!
I was wondering if there is a limitation to how many can be on a worksheet at any one time. Also to note, the shape I define the comments boundries also gets set back to the default occasionally.
How do i enable a date and time function to appear on the worksheet - so it updates constantly (either minutes or seconds). If this isn't available can anyone tell me website that contains the UK date and time and updates it constantly, I have tried the web query with [url] but for some reason the date doesn't come up.
View 9 Replies View RelatedI have a workbook with 120 LOI worksheets that all tie to one master data sheet, and I need to know the date and time of the last change on each worksheet and not the date and time of the last save. The date and time could either be located in a worksheet cell or in the individual worksheet header
View 4 Replies View RelatedA procedure residing in the "ThisWorkbook" module has just added a new worksheet into the active workbook. I now want this same procedure to write a set of code into this new sheet's code module.
Solution 1:
Create a worksheet before run-time that is already set up with the code you want behind it. Then, use this worksheet as a sort of "template" when you want to create a new worksheet as opposed to using the worksheet.add method.
Question:
I can use solution 1, but isn't there a way to insert the code on the fly?
I have a form & in a command button that form I have code that is supposed to copy a worksheet named template into some position among the other sheets in the workbook. I am trying to set the new worksheet to some variable. The code actually creates the new worksheet where it's supposed to but I still get a run-time error 424. If I don't try to set the new worksheet to any variable the code exits fine.
Works but with Error - next to Last line
Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
Sheet_before_name = ""
For Each wSheet In Worksheets
If UCase(wSheet.Name) > UCase(sheet_add_txt) Or _
wSheet.Name = "Template" Then
If Sheet_before_name = "" Then Sheet_before_name = wSheet.Name
End If
Next
Dim new_sheet As Worksheet
Set new_sheet = Worksheets("template").Copy(before:=Worksheets(Sheet_before_name))
End Sub
Works Fine
Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
Sheet_before_name = ""
For Each wSheet In Worksheets
If UCase(wSheet.Name) > UCase(sheet_add_txt) Or _
wSheet.Name = "Template" Then
If Sheet_before_name = "" Then Sheet_before_name = wSheet.Name
End If
Next
Worksheets("template").Copy before:=Worksheets(Sheet_before_name)
End Sub
I have read through post after post on this forum about this error but nothing seems to apply. I can use Dave's suggestion in another post of just using the template sheet as an actual worksheet template, but Then I would have to modify other code that deals with formatting. I would have to get it to apply any formatting changes to this workbook & to the worksheet template.
I have a rather large spreadsheet that takes a long time to calculate. One sheet has the data (about 2800 rows by 650 columns), with samples in rows and data for each sample in the columns. A second sheet has functions to group the data according to predefined patterns. That second spreadsheet is about 800 columns wide and as many rows as I need it to be, as the samples don't need to be analyzed all at once.
Ideally I would like to do all samples at once by making the second spreadsheet 2800 rows high, but the calculation time is just way too long. And there's the problem. The calculation time seems to increase exponentially with the number of rows I calculate at a time. I ran a few tests making it different sizes, and here are the results:
rows
time(seconds)
100
3.5
150
12
200
23
[Code]...
I tried both HLOOKUP and INDEX/MATCH functions and it didn't make a lot of difference. These numbers are for INDEX/MATCH.
Graphing these numbers show that it follows an exponential curve pretty closely. What is interesting is that the progress indicator at the bottom of the screen finishes in a few seconds, about 5 seconds for 350 rows, and I can see the new data after those few seconds. But then it sits and appears to do nothing for the rest of the time. So, for 350 rows, it looks to be done with the calculations within 5 seconds, but then it freezes for another 2 minutes with CPU usage maxed out before I can do anything with the program. That actual calculation time seems to scale linearly with the number of rows I calculate, but the time after it finishes calculating before it finishes whatever else it's doing scales exponentially.
The spreadsheet does have a macro and a couple of other sheets and a macro that do some later analysis and I wondered if that had something to do with it. To check that, I created a new spreadsheet and set it up the same as the original one, with the data in one spreadsheet and the INDEX/MATCH formulas in the other. I copied the data over, but setup the formulas new without copying/pasting anything. It didn't make any real difference.
I have a function which i am using as part of a macro. The macro itself works fine and locates a search string i type into an input box across ALL worksheets in an excel doc. However, i have a function which takes the results and prints it to the front page, but when i try to use the worksheet name which has been passed to the function, i get the error: Run-time error '42': object require. this happens when i get to a line of code which says
View 2 Replies View Relatedall i need to write is a code which syncronizes with my update button, which i don't know how to write....whenever i click the update button, all my files will be updates, and at the top elft of the screen would show the time and date of my last update.
View 6 Replies View RelatedThe user is asked for one piece of information "Enter the ID Number.
What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works.
Set Wks2 = Worksheets(strWks)
7 rows from the bottom.
My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks.
Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number.
I get a Time Run Error 9.
It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks.
Sub Macro01C_Auto_Resign()
Dim Wks1 As Worksheet, Wks2 As Worksheet
Dim strFind As String, rngFound As Range
Dim lngRow As Long, rngUnion As Range, strWks As String ............................
I am using the following code to enable users of a form to made make selections by adding a tick in one of the boxes by clicking on it. It works fine with thesheet unpotected but when I protect the sheet with cells E33:k33 unlocked i get Run Time Error '1004'
Unable to set the Name property of the font class.When I debug, Target.Font.Name = "Marlett" is highlighted.
I have a workbook with several worsheets in it. I wish to have the first worksheet as a list that will be hyperlinked to the other worksheets within the documant.
Is it possible to save the workbook so that it will open at the first worksheet every time irrespective of what worksheet you were on prior to clicking save.
determine the coding for a worksheet_change macro for the attached spreadsheet? I've got a macro set up in the worksheet and would like it to run any time there is a change in any of the cells on the worksheet.
View 4 Replies View RelatedI am trying to pull a specific time frame of data from worksheet, in a large file, into another active workbook. A fiscal month. I don't know how to at all. I figure it should be a And IF and Vlookup but do not know how to execute it all.
View 1 Replies View RelatedI work for a company that owns hotels and casinos all over the States.
Company wide I would say we have about 13,000 employees. The hotel I work at
has around 125 employees (even w/ the constant turnover after Katrina hit).
My problem is, that my corporate office refuses to update our payroll
operations. We still use punch time cards that every other Friday I have to
manually enter time into the payroll server timesheet. Our system does not
calculate paid time off, among a lot of other things. My employees come to
me almost daily asking if I can tell them how many PTO days they have left.
The only way I have to figure it out is by going through every PTO bi-weekly
request form spreadsheet until I have added up the days taken throughout
their service year.
I am trying to build a spreadsheet in Excel 2003, that consists of:
Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date,
PTO per yr, PTO taken, PTO days left....among other personnel information.
I want to create a formula that will calculate the PTO per yr the employee
has.
After 6 months = 2 days
1 year = 10 days
2 years = 14 days
6 years = 19 days
16 years = 25 days
26 years = 30 days
(If the employee does not use the days within their service year, they loose
it.)
I also want in the PTO taken column a formula that will pull the # of days
taken from the PTO request spreadsheet that I have to send in to home office.
The employee's differ every spreadsheet, as does their placement. Once the
days taken are in their proper column, I want the PTO left to be calculated
from PTO days per year minus the days taken.
