I have entered VBA code and tested it with success. If I then save & close the excel file and then re-open it, the mask time entry is not saved (time does not appear correctly after input) and per instructions I have designated (and am using) the cell input range.
View 9 Replieshow can i show asterixes instead of the input data as it is being typed in a cell? somewhat like a password box...
View 6 Replies View RelatedI need the users to be able to enter the time using either a . or a colon :,
So: 13:30 would give 13:30 and 13.30 would give 13:30. They have end up in time format too since I will be performing calculations on them. When that decimal place is entered I would like it to be turned into a dot
Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45.
I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this:
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 have a sheet that I enter time into in a 24hr time format, ie Military Time and cant figure out how to format the cell so that I can just type in the numbers and it automatically put it in the hour format. Currently I have to type in the hour then the colon the the minutes otherwise if I type in the time in a three or four digit format it just gives me 0:00.
View 9 Replies View RelatedI have tried to make a VBA that would allow me to type in:
10657 and have the VBA to automatically convert that into a timeformat -> 1:06:57.
It works ok as long as the typed number is 4 digits or shorter.
If i type 12345 I would expct to get 1:23:45 but I get 00:00:45
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VaValue As Variant
TCol = Target.Column
If Not (TCol = 5 Or TCol = 6) Then Exit Sub '
I'm creating a spreadsheet where a test note taker needs to repeatedly enter the date/time code in a column as events occur.
I tried recording a macro that will enter the =now() formula and then copy that over itself with the value but didn't work.
So now I'm thinking I need to just write code that will determine "now", and write that serial value to the next open cell (with care not to overwrite the previous data point). I'd like to execute this with a simple keystroke.
The thought is to start at the top of the column, go down until the next blank cell is reached, insert the time code (can't be a formula that will change but an actual value).
I've got a spreadsheet set up for a simple barcode scanning system. The user scans the barcode of the product when complete, and it logs it to a sheet along with a date & time timestamp. I'm then planning to use a v-lookup (barcode to product name) & pivot-table to count instances of 'product name' so that I can monitor what is being produced easier, nothing too tricky.
The problem I'm having comes from the products of the same spec having the same barcode, so data validation is hard to apply, as I can't 'not allow dupes' - the concern I have is that if the user unknowingly (or knowingly!) scans the same ticket twice, I think I've got 2 of a product when I've only got one. The idea I had was to lock all cells for a set time period (say 1 minute) after an entry so that it couldn't be accidentally scanned twice.
I want to be able to type a 24 hour time into the column and have it format with colons - for example: I want to type 123456 and have it show up as 12:34:56
View 5 Replies View RelatedHow can I get an automatic time & date entry into cell B1 based on a alphanumeric entry in cell A1. The time and date must not re-calculate every time the workbook is opened.
View 4 Replies View RelatedTextbox and SpinButtons, there is code for changing the date in a textbox by using a spinbutton. I have tried to use some variation of that for the purpose of changing time but to no avail. What my intention is, is that if someone enters 12:00 into TextBox that SpinButton_Up or SpinButton_Down can change the time to 12:01... or 11:59... respectively, and so on.
[URL]
I have a worksheet that I enter time values into individual cells:
A1 = 6:00
B1 = 12:00
C1 = 12:45
D1 = 17:15
When I enter these values, I would like some of the values to round to the
next higher "30 minute increment" such as Cell C1 should become 13:00.
I would also like some of the values to round back to the next lower "30
minute increment" such as Cell D1 should become 17:00.
I have cells on my spreadsheet which contain: =now() and they work as they should - displaying the date and time at the moment of entry. Unfortunately the cells update when other info is added at other locations on the sheet - is there a way to ensure they enter the current 'now' only at the point of initial entry - such that the data doesn't then alter?
View 3 Replies View RelatedI am looking for a formula or VBA that will be in each in cells D5:D300 and will look at the value entered into the cell in column C and change the 4 digits into the date and time in the same format as NOW() returns.
Example
2045 becomes 17/02/2012 20:45
But I would also want it to see if the 4 digits that represent the time are after 14:00 and before 00:00 then the date would be the date in cell A1-1
example
Cell A1 18/02/12
2045 becomes 17/02/2012 20:45
I'm making a data entry form for a user, and one of the fields is in a time format (mm:ss). Is there any way that I can validate that field in "cmdOK_Click()" so that when the user clicks "ok" on the data entry form, if they haven't entered a date a msgbox comes up.
I tried IsNumeric with an If function but to no prevail.
Is it possible to have a cell, say B1 - which will return the Time and Date Value of when something is entered into Cell A1?
Example, I type "Hello" in Cell A1.
Now B1 will show me the Time and Date which i typed in Hello into A1.
I need a function that records the time and date in a cell, say a2, when data in entered in to another cell, say a1 in the same spreadsheet.
This way I can tell when data in a cell in my spread sheet is updated.
I want to time stamp when I make an entry in to a cell. Example when I enter a name in a32 I want l32 to time stamp the time I entered the name.
View 10 Replies View RelatedI have a problem with entering 12-hour time data in excel.
If I key in 3:00, Excel will recognise it as 3:00 AM.
If I key in 3:00 p, Excel will recognise it as a text entry. To get Excel to recognise it as 3:00 PM, I have to use the 24-hour format. i.e. I have to type 15:00.
I have checked the Regional and Language option in the Control Panel. Everything looks fine.
Does anyone have such encounter with Excel before?
Is there an easy way to add every sixth column?
Example:
=A1+A6+A11+A16
Then
A2+A7+A12+A17
I am all the time doing something like this. Is there a formula or something of that nature that would sum every 6th cell down starting a a given point?
I'm having real trouble selecting a series of rows based on variables for the purpose of cutting them and pasting them elsewhere in the spreadsheet. For example, if this is my spreadsheet
row
2 I Hate
3 Bill
4 O'Reilly
I want my code to select the range of 2:4, cut them, and paste them elsewhere but I want 2:4 to be variables. For example
Fun = 2
Stuff = 4
Rows(Fun:Stuff).Select
Selection.Cut
But doing "Rows(Fun:Stuff).Select" does not work at all. If you do Rows(Fun).Select, this works to select Row 2, but I can not use the variables to select a range.
I would like to know how to write the macro that execute a formula everytime it detect an input in another column.
Attached is the example of what I want to achieve.
Macro to perform when entry detected.xlsx
This is the code I have so far:
Sheets("Oven Codes").Select
Rows("3:3").Select
Selection.Delete Shift:=xlUp
Sheets("Item Edit").Select
Range("I12:J12").Select
What I would like to do is have VB look to E1 to determine which row to delete. E1 could say 5 or 25.
I have a user form with 2 textboxes (used as parameters for an ODBC query). The entry should be a period, as yyyymm. How can I check, for instance when clicking the Ok button after entry, that both textboxes are of the "000000" format and the last 2 numbers are between 01 and 12?
View 9 Replies View RelatedI would like to have some kind of input mask on my input boxes, the input box is for currency IN GBP (£)
The program itself obviously has no use for the £ sign, but I would like it to display in the input box as a mask.
Is this possible?
I should mention I am using:
Application.InputBox
I am trying to get excel to auto populate the current date and time each time I make an entry, however, each time i make an additional entry the current date and time populates but it changes all the prior entry's with that current date and time.I am trying to keep a log of all the times I make a new entry. Correct formula that will work?
View 9 Replies View RelatedOk, so I'm a little cheezed that I've been volun-told to give up an economic model that took me over 8 months to develop to a few investment bankers that will likely sell it to the highest bidder. Of course, with no credit to me.
I've already taken a few of the necessary steps to lock it down.
Examples:
- Lock/hidden cells along with sheet protection (with password) - to prevent viewers from seeing the formulas I used.
- protect workbook (with password) - to prevent copy/paste of the sheets
- VBA - Tools - VBA Project Properties - Protection - Set password - to prevent viewing of the macros
- Hiding sheets using "Very Hidden" in VBA - to prevent viewing of sheets in general.
I guess my real question is, when this is all said and done, how easy is it for someone to break through all the passwords that I've set up in the workbook? If it's ridiculously easy, how do I prevent it (if at all)? What else can I do?
I have many files that has a filepath references in the cell values, for example:
='\serverfolder1subfolder2[Filename.xls]Sheet'!$B$82
Is there a way to change all the references to direct to another place?
Filenames don't change, only the '\serverfolder1subfolder2 path
I have a workbook with over 100 worksheets (all worksheet tabs are named). Since the tabs are not sorted in a numerical order (they are grouped by departments hence the numerical tab order does not apply), can I search for a particular worksheet tab using a search function? The CTRL F function doesnt allow searching for worksheet tabs. At the moment, I have to go through the tab names in order to find one particular tab.
View 3 Replies View Related