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).............
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.
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..............
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
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?
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.
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
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
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?
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?
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'
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.
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?
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.
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
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?
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),
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.
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?
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.
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?
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.
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???
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.
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
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....
