I have an input box that takes in a file name. Obviously, sometimes people put in illegal characters which can't be used in a file name and I need to deal with this. I can check the variable once they have pressed enter but is there a way to check it as they type it in?
It would be very nice to either have nothing happen if they type in a slash or for a msgbox to pop up, as it does in Windows,
Remove Illegal Characters From File Name ...
View 5 Replies View RelatedDoes anyone have an example of dependant drop boxes with illegal characters in them? Even just a teensy one... If I have an example I can manipulate I can actually see what does what... The several-paragraph-long explanation with descriptions of what's being referenced by what and where and why - isn't helping me any.
View 6 Replies View RelatedI have a VBA code that works really well but i wondering if there is a way to do the same thing but without the message box and just enter in the dates instead.
View 11 Replies View RelatedBelow, both inputbox cases appear to work fine. If you enter an incorrect input on your first attempt it will offer you the option to return you to an inputbox.
View 3 Replies View RelatedI'm using the following code to create an Inputbox but I want to either disable the OK button until an entry is made or not allow a blank entry. Forcing the user to have to make an entry.
If NameTextBoxnew.Value = "" Then
NameTextBoxnew.Value = Application.InputBox("You must input a name/nickname to identify person", "Name")
End If
If SexTextBoxnew.Value = "" Then
SexTextBoxnew.Value = Application.InputBox("You must select a sex(m/f)", "Sex")
End If
If DOBTextBoxnew.Value = "" And AgeTextBoxnew.Value = "" Then
AgeTextBoxnew.Value = Application.InputBox("Please insert a rough age in years", " Date of RSIT")
End If
Here is my delimma. I am using the standard InputBox for my users to input the number of errors found for each category in a record from a daily report. The problem I having is that every time the user types in data using the InputBox it overwrites the previous days numbers. I need to set up the InputBox code so that after the user has input the numbers for that day that the next time the InputBox is used it selects the next column to the right and continues to do this for each day.
View 4 Replies View RelatedI have the inputbox so i can set a string value,
When the inputbox Cancle button is pressed i want to exit sub,
If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname.
If the inputbox has a value do >>>>>>That>>>>>
A user inputs a string into a message box. A particular cell on a worksheet is then given the value of this string. I would like to have this cell automatically format to Wrap Text if the string is longer than 100 characters and skip to the next line after each 100 characters. However, if 101th character is part of a word, then that word would be on the next line. If the string is less than 100 characters then the cell is simply given the value of the inputted string ( I know how to do this )
View 3 Replies View RelatedTrying to pass an array to a procedure to use as criteria in advanced filer.
My code below is raising this error:
Run-time error '1004':
The extract range has a missing or illegal field name
rngData. AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False
Private Sub btnOK_Click()
Dim arrValues()
Dim lngI As Long
Dim lngX As Long
With Me.lstAccounts
If .ListIndex <> -1 Then
For lngI = 0 To .ListCount - 1 ................
"Extract range has an illegal or missing field name"
I am trying to use a simple advanced filter (unique) function. Any ideas on why I could be getting this error?
If the user enters a numeric value in Range ("B" & Rows.Count).End(xlUp).Offset(1), I need the code to copy the formulas in Range ("A12,C12:V12") to Offset row If there is no user entry in Range ("B" & Rows.Count).End(xlUp).Offset(1) then nothing should happen.
This should be monitored in sheet at all times.
I`ve done som research and think the code must be entered as a Private Sub Worksheet_SelectionChange(ByVal Target As Range) sub?
I have no experience with macros at all but i need to create a macro for work that will keep the original data entered but work out the difference between new data collected each day ...
View 10 Replies View RelatedI have a requirements to do a simple monitoring data entry. Composed of 1 admin and 10 users. These 10 users are assign to a different lines basically every lines have assigned user.
These 10 user will do a data entry. once they are done on the data entry the Admin will update those entered by the users based on ticket number and the users can browse those updated records.
Every users per line have assign ticketnumber that start in "L1"&YY&MM "-"001" this compsed of lines + year+month and numbers.
For the users side. Right now i have already created a workbook with userform to perform data entry by user (samplefor line1 and line2 user).
It has also a viewing form to view by the users if its already updated by the admin. i put also an events if the admin work book is open by other user they cannot access the workbook.
For admin side. I created also a userform for updating the records entered by the users to update records admin will enter ticket number to textbox.
This is the reference to find and search those records that belong to a particular ticket number and lines which will be updated by the admin.
Right now, I am doing records consolidation for both user and admin records. I have a button for both admin and userlines to perform consolidation. Once the user entered the records admin will capture those records and copy to the workbook used by admin to perform update. Then once the admin perform update the user will capture those updated by admin and copy to the workbook under the user.
Basically they have separate workbook for their entry and database.
Right now I encounter some issues which I think this will affect the integrity if records. Sample like once i perform consolidation it create duplicates records.
I have data in a worksheet coming from an external device that is updated via DDE. The values in the cell change every few seconds. I would like to record the highest and lowest values that these cells contain.
I want the peak values to be stored in other cells.
I wish to monitor a change in a certain columns at some worksheets. I have - after having consulted John Walkenbach' fantastic book Excel 2007 Power programming with VBA - understood how I do to monitor a change in specific worksheet, by using the Intersect-function in the change-event on that particular worksheet. That works fine.
My situation is that I have an application that I start out with a certain number of workheets in a workbook, and in the course of the work that the application does, it now and then adds worksheets, and these worksheets I would like to monitor for change in a special column. I know, by looking at the names of the worksheets which ones of those in the collection that I want to check.
I therefore used, not the Worksheet_Change event but the Workbook_SheetChange. It doesn't work quite well. I guess my question for now would be:
The call to this event looks like this:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
1) How - in what way - should I use the first argument, the "Sh as Object"? Can I/Should I give it a name?
The error message I get when I use an instruction like this:
If Not Intersect(Target, Range(M:M)) Is Nothing Then
'Do Stuff
end if
The method 'Intersect' in the object '_Global' failed
(M is the column that I want to check)
Is it possible to use the Worksheet_Change command to monitor 2 different cells in the same worksheet and call a different procedure depending on which cell is changed. i.e. if cell A1 is changed do this, if cell a2 is changed do that
View 9 Replies View RelatedWe have a few residential care homes across UK. I have a workbook where I store all our residents fees which I update daily. There are 2 worksheets on one I store list of residents on the other one I have a daily breakdown.
What I'd like to do is to use worksheet "daily" to sum fees from "all residents" with criteria like:
- carehome ("daily" row 2),
- fee ("daily" F:K,N:S,V:AA etc)
- date ("daily" C7:C1073).
While fees are on worksheet "all residents" where:
- column A has carehome name on it
- column M to AG has fees on it
- row M3:AG3 has dates on it
For example id like to count in cell F7 how many residents in Branch Court "Bc" were in residency on 12 March 2012 with £520 fee looking on worksheet "all residents" with criteria fee, carehome,date)
Each time one of the council backdates fees or changes a resident to lets say a private one I have to manually change all the numbers which takes time.
res.xlsx
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
I'm trying to use conditional formatting to highlight cells in a column that have less than 8 characters.
I know the LAN function, but I don't know how to make it work for the conditional formatting.
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
I have to design an input user form (a button that opens an input box or dialog box), which asks user to "Enter number of "columns"".
User enters a number i.e. 2 or 3 etc
It then asks user for the headings of these coulmns and places first column heading in A1 and second column heading in B1, third in C1 and so on ...
I am just starting to get my feet a little damp with VBA and I am trying to make a macro that will act whenever any text is entered in any cell within a 3x3 square. Whenever any text is entered in any of those cells I would like it to enter a text ( "X" ) in a cell which is specified by a cell in the spreadsheet (this cell will output which cell the macro should write the text in in this format "A:1" or "C:3"). How can I make the program act only in reaction to the user entering thier text, and also, how can I get the macro to read A:1 and enter the text into that specific cell? Any help would be so awesome, the book I have is really difficult to navigate.
View 9 Replies View RelatedI have another dilema. I am writing a macro that essentially takes a template from a workbook and copies it to another workbook that already has data. the data is then imputed into the pasted template through the macro... my dilema is that I have numerous data sheets so at the begining of the code I need the macro to pause so the user can manually select the cell or range that the copied template is to be pasted too... I was thinking an inputbox would work but I am unsure how to write this part of the code.
View 3 Replies View RelatedThe report is a "template" that will be ran once a month every month and will look back at the previous month's data. What i've invisioned is writing a vb script to load on workbook open asking various questions via "inputbox()" method.
SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20070731 10:10:51.450'
SET @EndDate = '20070731 10:11:51.450'
SET ROWCOUNT 100
SET NOCOUNT OFF
SELECT EventTagName = EventHistory.TagName, Value
FROM EventHistory
INNER JOIN AnalogSnapshot ON EventHistory.EventLogKey = AnalogSnapshot.EventLogKey
INNER JOIN SnapshotTag ON SnapshotTag.SnapshotTagKey = AnalogSnapshot.SnapshotTagKey
WHERE SnapshotTag.TagName IN ('SysSpaceMain')
AND DateTime >= @StartDate
AND DateTime
I have a macro that sends data from excel using MS Outlook. I want to add a more specific Subject header in outlook, and do this using an input box.
I thought adding something like this would work, but it doesn't... (below are just extracts from a very long code...)
Dim CurMonth as String
CurMonth = InputBox("Please enter the reporting month","")
.Subject = "Trial Balance - All companies" & CurMonth
When outlook generates the email, all I get in the subject line is "Trial Balance - All companies"
I am trying to create an inputbox using code from this site:
Sub NumericDataType()
Dim lNum As Long
On Error Resume Next
Application.DisplayAlerts = False
lNum = Application.InputBox _
(Prompt:="Please enter you age.", _
Title:="HOW OLD ARE YOU", Type:=1)
On Error Goto 0
Application.DisplayAlerts = True
If lNum = 0 Then
Exit Sub
Else
MsgBox "You are " & lNum & " years old."
End If
End Sub
I get an error on the first line that says, "Compile error: argument not optional".
I am trying to simply the data input for someone who is not very experienced.
I have had some success but need to have an extra option in case of mistakes.
Ideally I need the following -
I want an inputbox that will appear when the user starts the macro and for example asks Enter Surname
Then I know what I want but I don't know how to do it.
I want a box to display the Surname that the user typed into the inputbox with a message asking Are Details Correct? with a Yes and No Button.
Clicking the Yes button moves onto the next inputbox clicking the No button cancels the whole macro before it enters data into cells.