I want to do is prompt the user for an Excel file and then insert it as a new worksheet as sheet 2. Also, if possible I would like the imported file to insert to the right of sheet 1. Anyone know how to do this or at least know some parts of this problem.
View 9 RepliesI have code that prompts the user to open a specific excel file. However, with the following:
Filepath = "..."
name = Application.GetOpenFilename(FileFilter:="Excel Files *.xls* (*.xls*),",)
Workbooks.Open name
the default directory you start out in in the window that prompts the user to select a file is NOT ...
How can I properly assign which directory the user starts in so they need not go through 10 sub-directories everytime they run this code?
I am trying to have an operation run when opening the Workbook. The UserForm opens fine, but I get an error when the GoButton_Click sub runs. The error file is not very comprehensive.
The user is promoted to 'Delete' or 'Cancel' before the sheet is deleted. I do want to select 'Delete' for the user, but I'm not sure how to do this.
The error mentioned occurs after the user selects 'Delete'. The error is:
Run-time error '-2147417848 (80010108)':
Automation error
The object envoked has disconnected from its clients.
VB:
Option Explicit
Private Sub UserForm_Initialize()
Me.Choices.List = Array("Choice 1", "Choice 2")
End Sub
Private Sub GoButton_Click()
If Templates = "Choice 1" Then
[Code]....
how do i go about setting a message box to pop up every 10 mins or so prompting the user to close the workbook.? i want it to be as irritating as possible! iv'e had a look at ontime but cant get my brain into gear!also don't some of these timers fire even if workbook closed?
View 4 Replies View RelatedBelow is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1()
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located
newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls"
Set fs = Nothing
End Sub
Does VBA support any type of "browse for file" interface? I'm thinking something similar to an inputbox, but instead it gives you the standard looking windows explorer window to find a file (think adding an attachment in outlook).
The function would return a string.
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
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 RelatedI have an excel file with multiple sheets. I want my HR team to update that file, but one user shouldnt see the entries of other users. I want to make a consolidated file out of three files. This excel template is for salary input.
View 3 Replies View RelatedAn Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it
View 1 Replies View RelatedHow do I pause my macro until the user selects a chart in the worksheet? Or Keep prompting a message to the user till he selects the chart and when a chart is selected run the macro.
Step 1: Check if a chart is Selected
Step 2: If yes, run the macro
Step 3: If no, prompt the user with a msgbox of Retry/Cancel.
Step 4: If the user selects Retry, wait till he makes a selection.
Step 5: If the selection is a chart, run the macro. If the selection is not a chart prompt the same msgbox again and keep looping it till he selects a chart.
Sub test5()
Dim chtSelected As Chart
Dim UserResponse As Integer
On Error Resume Next
Set chtSelected = ActiveChart
[Code] .....
I would like to do a Macro which requires user to go to a specific line which is variable in each work sheet. I would like to be able to enter the number and then delete all lines below that point and then continue with the balance of the macro.
Is there an easy way to pause the Macro and then restart it? Or is there a code that I can insert in the Macro to locate a specific Cell and then delete all lines below the next line?
In my macro, I'm trying to use an input box to determine the factor to multiply another cell by. I also want to only allow entry of 6.0 or less, and only allow, (but not require), a single decimal place. Here's what I've got so far, but it's not working:
View 2 Replies View RelatedI would like to make a button on my toolbar that will let me autofill whatever cell I've selected down to whatever row I designate. I would like to have a user input where you put in the row number and it autofills whatever formula down to that row in the column selected.
I know how to do a normal autofill, but I don't know how to do a user input or have it input into the autofill process.
This code works fine if i use static ranges, but i would like the user to input the column in which the data is stored.
'Get data for the locations of the gaming license numbers needed for the comparison
varPlayerHost = InputBox("Please enter a single letter for the" + vbCrLf + _
"Column that the Player Host License" + vbCrLf + _
"numbers are in.", "Player Host Number Location", "H")
varHostLicense = InputBox("Now enter the column letter for the copied employee" + vbCrLf + _
"license numbers", "Employee License Number Location", "U")
'Set the ranges for the data to be compared
'This is where I am getting the error???
Set rRangeA = Range([varPlayerHost,1], Range(varPlayerHost, 65536).End(xlUp))
Set rRangeB = Range([varHostLicense,1], Range(varHostLicense, 65536).End(xlUp))
'The actual comparison and deletion of record that match the license numbers copied.
'When this function finishes you will be left with a spreadsheet that only has patron
'information left for the patrons that have an invalid host number.
For Each rCell In rRangeA
If WorksheetFunction. CountIf(rRangeB, rCell) > 0 Then
rCell.EntireRow.Delete
End If
Next rCell
This exact code is kicking an error
"Run Time error 1004"
"Method 'Range' of Object '_Global' failed"
Not quite sure where i went wrong.
If anything else is entered into the input box a message box (MSGbox) with a vbCritical button will appear with the following prompt "You entry (insert entry data here) is inccorect." I don't know the code for "insert entry data here".
View 4 Replies View Related I would like a cell to have user input. If there is no input then the cell should reference a value in another cell. My strategy was to use a formula in another cell to check to see if the user input cell was empty. If it was empty then the formula propagated the cell. If it had user inputted data then it stayed like it was.
Here is an example:
Begin Invent
1000
Gallons
End Invent
User input or
formula here
Gallons
Received
Gallons
Used
Gallons
I want the user to be able to input a value for the End Invent cell right below 1000. If there is no value I want that same cell to mirror the cell above it with the 1000 in it.
I want to administrate a short training test. The user has chooses a correct answer A-E, off a drop down menu. I want to set it up so the user can't answer multiple times until they choose the correct answer.
View 14 Replies View RelatedI have a cell which team members enter their team name in to. For example FR1 and RA5. I set up data validation to only allow four characters so they can enter RA10 FR15 etc. However I have now found some are entering FR 1 with a space between the team name and number. This then means my formulas further on are not picking up the cell correctly. How can I have the space between the FR and 1 removed automatically upon user input?
View 4 Replies View RelatedI need a cell to display a default value (something like "Input Here") which a user can overwrite with a value. When the value is erased, the cell should return to the default. Preferably, it'd be great if the cell reset to the default value whenever the workbook is opened as well (i.e. won't save the user's input value).
View 2 Replies View Related I am trying to build a user-friendly Excel database that takes the user's input from various cells and logs that data input by the user in successive rows to the side. Please see the screenshots of my current spreadsheet below:
Compressor Database.jpg
From the screenshot above, I would like for the user to input their data for the Date of Last Inspection, Current Hours, and check the boxes for any services they performed in the orange/pink "Input" box on the left. Currently I have the spreadsheet set up so that the orange/pink "input" areas recognize data put in to the yellow "Data Log" cells to the right. I am trying to get the yellow "Data Log" cell area to store the Date and Hours data put in by the user in successive rows like a database, but I am not sure how to do this. I was thinking of putting a button in the says "Log Data" which would, when clicked, store the data in the "Data Log" cells. I am unsure if I need to use a Macro or whether Excel has a simple, built-in function that can be used. Eventually I have plans for the "Date of Last _______ Change" Gray boxes, but would like to take this one step at a time.
I am not well versed in VBA and Excel Macros, and I am currently using Microsoft Excel 2007 (yes, I know it is old). I do have C# and Matlab coding experience, however I would like to do this in Excel to make it easier for the User as it is a well known program.
I want to present a user with a list that they can make a selection from and enter their selection in a cell. Sounds easy but.........
View 9 Replies View RelatedI have a very simple macro for recalculating formulas and printing the page.
I need a way to show an input box where the user can say how many times to repeat the process i.e. 2x = recalculate, print, recalculate, print.
I am writing a macro that will perform a loop operation to export files. I want the user to specify the path, and the macro will save each exported file to that folder.
I did a number of searches and I can't find exactly what I'm looking for.
I think I want to use ChDir after using CurDir. But I don't know how to code the macro so that it asks the user to specify the location.
I have a macro that's running quite well and is cross-posted here:
[url]
What I need to do is allow for user input. I want to create a dialog box that pops up and asks for an input range, when the user runs the macro. Values can only be from 1 - 50. And the user won't be allowed to select broken sequences, like 6, 9, 12-15.
They would have to be continuous, i.e. 6 - 15, etc. I'm still very new to VBA. I can play with the control toolbox, but I have very little understanding of how to interface with the components that I'm adding/creating. Once I have that range, I want to use the inputs as the 'start' and 'stop' points for my loop. So I need to capture the values and pass them as input parameters, I'm guessing.
I can modify the loop to accept the inputs, I'm just not sure how to capture them from a user input dialog box.
Due to the economy we are only scheduling days to work one week ahead which leads to alot of phone calls. The gaurds who get the calls then search thru a printed spreadsheet looking for a clock number or last name. I realized right away they should just look at the file on the computer and due edit - find. The problem is they due not use computers unless printing and they looked at me like I was talking in a different language. So I want to edit this macro below so it will allow user to type in search criteria instead of using same search criteria every time. Here it is 70039. I also need to place a shortcut or button to this macro on the toolbar or sheet and do not know how. How do I stop "remove macro or enable when file is loaded"?
View 3 Replies View RelatedI'm trying to get a users' selected range to display in a cell as stored text.
therefore, If a user selects A1:D7. The text in Cells("G", 10) will be:"A1:D7"
w/o the quotes.
I've attached an example workbook.
Here's the formula thus far:
Sub SelectRangeBox()
Dim rnBody As Range
Dim vaMsg As Variant
Do
vaMsg = Application.InputBox( _
Prompt:="Please enter the message-text:", _
Title:="Message", _
Type:=2)
Loop While vaMsg = ""................................
Actuall maybe my thread should be Titled. user selected cell range displayed in input box save to cell as a text value.
I work in the Quality dept and I have came up with a gauge calibration workbook. I am in need of a command button to get an user input "date" and sort the data and print by that date. What I have now is you have to manually sort by the date needed and copy and paste to a worksheet.
View 11 Replies View RelatedI need a way to divide-up an area exactly after user input. If an answer exists, once I know how to divide say the width, I can use the same solution to divide the height. In that case I'll concentrate here on the width only.
- The user puts in a width of say 11300mm [11.3 meters], I then need to divide that by a maximum with, say 2000mm [2 meters].
- Obviously that doesn't fit exactly coming to 5.65 and I need a whole number, thus I then need excel use a figure under my maximum of 2000mm until dropping about 1883mm, it gives me the whole number of 6 rather than 5.65.
I'm no expert with Excel but I do use things like LOOKUP, VLOOKUP, IF, ISNU, validation list and things like that.
Basically this is to divide the area of a flat roof into lead bays, and as lead bays have a maximum size, I need a way to calculate how many bays there are vertically and how many there are horizontally off user input (width & height).
What I am doing is creating a spreadsheet solution to tack race times. These show up in past performances in the following formats in paper copies: 45 2/5 seconds. The end user would have to put in maybe 30 of these times based on the number of contestants. Keeping in mind that the 45 2/5 number will be used in some calculations, I want to give the user the easiest input interface. Meaning instead of inputting like this; 0:45.4 (mm:ss/00 format), I would like them to just enter 45.4. the spreadsheet would input format this cell into a time format that would be able to have calculations done to it. Another example might be 1:35.60 (mm:ss.00 format) which is 1 minute 35 and 3/5 seconds. This would be entered as 135.6.
A few questions first:
1) Would it be easier to leave the number inputed alone until the calculations ar finished then translate into a time factor?
or
2) would it be necessary to convert into a time factor as inputted be cause of the 60 factor involved with the time factor?
Having asked those questions, setting this all up with the exact scope I am working towards, any help or sugesstions as to the best way to proceed would be fantastic!