Collect Date From User & Check If Valid
Aug 31, 2007
want to do something simple, and it just wont paste. not sure why. anyone care to look it over and tell me what i'm doing wrong here?
'Insert Date Information
Dim vDate As Date
Range("D2").Select
ActiveCell.FormulaR1C1 = Date
vDate = Date
'Get date from user
Dim dDate As Date
vDate2 = Application.InputBox(Prompt:="Type in the due date for the location." _
& Chr(13) & Chr(13) & "*If you want the date to default to " & Date + 5 & " then leave the field blank.", _
Title:="Due Date", Type:=1 + 2).............
View 3 Replies
ADVERTISEMENT
Jun 27, 2008
I'm getting a cell address from user. I need to check whether the cell address is a valid cell address.. How do I do it.
Dim s As String
s = InputBox("Enter a valid cell reference")
If (S Is a valid cell reference) Then
MsgBox "success"
Else
MsgBox "Failure"
End If
I tried doing this using Left and Right string functions. But thats going very complex.
View 3 Replies
View Related
Sep 9, 2006
I have an existing macro which I am enhancing and I would like to have the user provide a date, either with a popup text box in a userform and then use a command button to hit OK and have the box dissapear, or in a calender which the date is selectable, and the date supplied go into a variable.
View 2 Replies
View Related
Jun 21, 2008
We have an excel file which is made every thursday for comparison purposes etc. I've made an macro (excel 2003) which - on start - asks via an inputbox for the extension in date format for saving purposes. Later on, the macro will search for the same file but from last week to open it and copy/paste a 'comments' column into this new one. All went well, everybody happy. However it may be well possible that file is made and saved but with an extra extension: example:
dir to save = C:file
file when starts runs inputbox "Please enter a date (e.g. 2008-06-20)"
The macro records this string as mydate:
Sub Start()
Dim AnyString
Dim MyStr
Dim DirString
Dim mynum 'As String
Dim resp As Long
Dim get_mynum
Dim mydate
'Define extension for the file name to be saved and the correct path (dir) where this file will be stored.
get_mynum:
mynum = Application.InputBox("Enter the filename's extension to save in yyyy-mm-dd (e.g. 2008-06-30)", vbOKCancel)
If mynum = False Then
msgbox ("You do not want to continue? Ok, programm stopped")
Exit Sub
ElseIf mynum = "" Then..............
View 9 Replies
View Related
Aug 19, 2012
Any way to check if data entered is a valid date using a VBA function/routine?
For example, cdate() and isdate() functions accept dates like 31-Feb-12 and 29-Feb-11 when they are not valid.
If the cell is formatted as date format, then Excel replaces hyphens with forward slashes for dates, except invalid ones like 31-Feb-12 and 29-Feb-11, so in these cases I can test for existence of forward slashes in the following way
Len(Mid(pCell, InStr(pCell, "/") + 1, Len(pCell) - InStr(pCell, "/"))) 7
However this approach fails for Input boxes. Obviously I can't convert the Input box response using cdate function as it will incorrectly accept 31-Feb-12 as a valid date.
Any routines that will work for both cell entered and Input box entered dates?
View 5 Replies
View Related
Feb 17, 2010
I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
View 2 Replies
View Related
Dec 8, 2006
Date = InputBox("Enter the production month date in YYYYMM format", "DATE") 'This is the only info I want them to enter
Windows("PCN4.xls").Activate
Sheets("Bring the data in here").Select
Range("A" & NextLine).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;W:C30MonthlyMonthly NEW TCs200610 otconsa.txt", Destination:=Range _ ' I only need the 200610 to change every month
("A" & NextLine))
.Name = "totconsa"
End With
View 9 Replies
View Related
Jul 7, 2007
I create a macro which ask the user to input a formula(user can select a range in the excel screen), then I copy this formula to a specific row of every worksheets.
The problem is Application.InputBox("Insert a Formula", "This accepts Formula", 0+2) can not return the formula, it will automatically evaluate this formula and return a value!
This code is:
Sub CreateNewItem1()
Dim bFormula As String
hi=10
Application. ScreenUpdating = True
' here is the INPUTBOX-method :
On Error Resume Next
Application.DisplayAlerts = False
bFormula =Application.InputBox("Insert a Formula", "This accepts Formula", 0+2 )
On Error Goto 0
Application.DisplayAlerts = True
MsgBox "You have inserted bFormula is:" & bFormula
'Cells(hi, 2).Select.FormulaLocal = bFormula
' MsgBox Cells(hi, 2).Select.FormulaLocal
Call CreateNewItem2((hi)) 'call the copy formula macro
End Sub
View 7 Replies
View Related
Jan 29, 2008
I have an input box for a password, and my vb code checks the password against a list, and if it is correct allows the user to continue. I want to change the input box for a user form, as it allows more text on the form, and I also want to include a checkbox that the user must tick to show that they have read and understood the instructions. I have defined the form, but am having trouble getting the form to interpret the user input.
The form has one checkbox, which needs to be ticked to continue, otherwise give a message saying that you have to confirm. It also has one textbox, for the user to input the password. I dont understand how to name the contents of the text box, and use it in the following code. Also do I need a "send" button on the user form, for the user to click after entering the password. If so how does that work?
View 4 Replies
View Related
May 20, 2008
I have a macro that creates a new sheet and names it based off a cell value (date, MM-DD-YY), and copies a 'Template' sheet to it, values only. Every so often, two sheets need to be made with the same date. I need the right direction in creating an addition to the below code that will add an A, B, or C at the end of the sheet name if the name is already taken?
View 13 Replies
View Related
Oct 29, 2012
I've 1000's of website URLs which I need to open one at a time, and check if the page contains message such as 'Product not found' or 'Oops...' or 'Page not found' or 'Error'
Is this possible using Excel / VBA ?
[URL]
View 2 Replies
View Related
Jan 22, 2014
I have a large spreadsheet that has thousands of hyperlinks that I would like to check periodically to make sure they are up to date. I have found dozens of examples of VBA code that will do this for me and seemingly very clear instructions on how to do it (for example this), but haven't yet been able to successfully run anything in my spreadsheet. It either doesn't do anything, or I get an error message of a bad file name.
View 2 Replies
View Related
Dec 10, 2007
I have this formula =COUNTIF(WallA,D35) which works great unless the named range is deleted. Is there a way to check to see if the named range is valid in formula?
View 9 Replies
View Related
Apr 28, 2006
I was wanting to use an IF Then statement to check if numberes entered into cells in Sheet1 were valid numbers in another sheet. The valid numbers will not always be consecutive.
View 2 Replies
View Related
Aug 26, 2007
I am using VBA to determine the data type of the active cell value. I have the following:
'To check Number
IsNumeric(ActiveCell.Value)
'To check Date
IsDate((ActiveCell.Value)
'To check Formula
ActiveCell.HasFormula
'To check Text (*not perfect solution)
WorksheetFunction.IsText(ActiveCell.Value)
However, I cannot find the way to check for Time. In addition, the method IsText will return True even if the active cell value is a date.
View 8 Replies
View Related
Feb 18, 2014
I have a cell in which I would like to have a user enter an email address. If it does not follow a valid format I would like it to display an error message, "Invalid E-mail Address", clear contents and re-select the cell otherwise move to the next cell. By Valid email I want it to check for only one "@", and at least one "." but I also want it so that a user can't just bypass it with typing "@.". I would like it to reject special characters and spaces and only allow "-" and "_" maybe a general format of <name>@<place>.<domain>
The cell in question is K45 and the following cell would be K47 if a valid email is entered
View 8 Replies
View Related
Feb 18, 2009
I'm working on a spreadhseet which has several macros that can take up to 5 minutes to run. When the macro(s) complete, I pop up a userform to provide instructions and collect some additional data from the user.
The "problem" is that when the user moves to another application to do other work, the user form remains hidden behind the active app. I've added some text to the status bar to let the users know the processing is done, but they would like something more obvious - so, is there any way I can force focus back to Excel?
View 2 Replies
View Related
Mar 4, 2008
I am trying to get the user to enter a relevant date but code returns date in US format. That is mm dd yyyy instead of dd mm yyyy.
View 5 Replies
View Related
May 2, 2008
I have created a standard input box for the date (UK)
datereceived = InputBox("Please enter the DATE the text message was received, please enter in format DD/MM/YY", , Date)
Sheet1. Range("e" & x).Value = datereceived
I have formatted the cells for row 'E' for 'Date' and DD/MM/YYYY in English UK, But when I have inputted the date into the input box ( e.g. 01/05/2008 ), it inputs into the spreadsheet 05/01/2008 (American Obviously),
View 5 Replies
View Related
Feb 27, 2008
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
View 5 Replies
View Related
Apr 28, 2014
The question is plain and simple. I have this date:
Oct 25th 2011 11:28
I want to convert it to a valid date format so I can use it for further processing.
View 2 Replies
View Related
Jan 9, 2008
When a user inputs a month and then a day, I want to be able to check to make sure that the day entered is possible in that given month. Is there a way to do that which is not too complicated?
View 9 Replies
View Related
Jan 23, 2010
Please refer to attached spreadsheet
I receive data from an external source and it displays what appears to be dates in column B.
In column G I apply a formula to display month/year info.
It turns out that some of the data in column B is valid date data, but other data imports as plain text and therefore I can't get the month/year info that I require.
I have attached just a small example.
I get this data monthly and it usually covers thousands of rows and therefore impractical to change manually.
View 6 Replies
View Related
Feb 19, 2009
I have a SAP application that outputs a field of data from a specific query. One column, the date, is produced in the format 18.02.2009. Is there any excel 'trickery' that can convert this into a standard british date format i.e. 18/02/2009 & then allow me to sort the entire data field by ascending date order?
View 6 Replies
View Related
May 31, 2006
I am using the following code which works fine:
'Checks user name before opening
UN = Environ("USERNAME")
If UN = "lemons" Then
Sheets("Title").Select
Range("a1").Select
Else
ActiveWorkbook.Close
End If
But when I change the code to check for more than on user as shown, I am getting a complile error.
'Checks user name before opening
UN = Environ("USERNAME")
If UN = "lemons" Then
Sheets("Title").Select
Range("a1").Select
If UN = "LEMONS" Then
Sheets("Title").Select
Range("a1").Select
Else
ActiveWorkbook.Close
End If
I need to add or a better way to check the user name
View 3 Replies
View Related
Feb 28, 2009
My problem is trivial but annoying. When I initiate my user form, one of the check boxes has a dashed border around the outside as if it was selected. No matter what I try, I cannot get the form to initialize without effecting this box. If I delete the box and recreate it, another box simply inherits this problem.
I have attached the spreadsheet, please Click the button over "A1" to see what I mean if you have the time.
View 2 Replies
View Related
Mar 19, 2009
i am writing a program that asks the user for an input and sets it equal to a variable.
i want to look thru a column for this variable, if it is not in it, then paste it at the end of the column. if it is in already, throw up a dialog box saying its already used and exit the sub.
i dont know how to set up this kind of thing up???
vlookup in combination with an if statement?
View 6 Replies
View Related
Mar 9, 2013
I was told that the following code would enable my macro to execute every time the user interacts with anything Excel:
Code:
' Repeatedly check for user interaction whenever a change is detected
Private Sub Worksheet_Change(ByVal Target As Range)
Call TestMacro
End Sub
It doesn't work...
To put it into context, he is my entire program:
Code:
Sub TestMacro()'
' TestMacro Macro
'
' Create string variable
Dim undoText As String
' Assign the text to string variable
[Code] ......
My being new to VBA doesn't work in the situation as my programming instincts tell me to set up an infinite loop to continually check for interaction something like the following:
Code:
while( x == 1 )
{
do MY_CODE;
if( USER_CLOSES_EXCEL )
x == 0;
} // end while
However, Excel is apparently 'Event Driven' so I'd love to know how to use this to my advantage in keeping my macro running.
View 2 Replies
View Related
Jun 6, 2008
I am trying to ask to the user to check if they logged out when they close the workbook but my code is not working...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("OD&D Log-in").Select
If Range("H5") = "reconcile" Then
a = MsgBox("Do you want to Log-Out?", _
vbYesNo)
If a = vbNo Then Cancel = True
If a = vbYes Then
Sheets("OD&D Log-in").Select
Else
Workbooks("Daily OSD Log (ver5).xls").Close SaveChanges = True
End Sub
View 9 Replies
View Related
Jul 28, 2004
I have a program that opens an excel-workbook, I first check whether the file is opened by another user(open for read-write). This works fine, but I'd like to know which user has the file open with VBa code. ex.
workbooks.open ....
if open
then msgbox " Book opened by user"
end if
So it's the same as you open an excel(with your windows explorer) on a network and you get the message that the file is already opened by the user....
View 2 Replies
View Related
