Creating A User Interface For An Excel File
Jun 12, 2008
I have been tasked by the company I work for to come up with a simple to use interface that can be used by someone with little understanding of excel to set the autofilters to certain settings.
So here is what I have:
2 sheets (each with the same number of columns but different data which corresponds to good and no-good)
Each column has an autofilter but only two on each sheet need to be set.
One filter controls valve type (of which there are 10) and the other controls running condition (startup and normal)
Now my employer wants something that is basically idiot proof so that someone can simply open up the excel file, input the valve type and running condition, and be rewarded with only the corresponding data and charts. Now the filters are already in place, the charts already exist and the only thing left is the user interface.
View 14 Replies
ADVERTISEMENT
Jun 8, 2007
I am trying to create a user pop-up box that can accomplish the following:
a. Pop up box that requests an input for a particular machine number, type of error and also a particular calculation type (STDEV,Average,etc).
b. The result will be displayed either in a tabular format or if applicable, a chart
I have 10 worksheets where each worksheet is a different mahcine ID. Contained within the worksheets is various data-sets(Data listed by week). I am trying to enable my mangers ability to not have to look through my data or choose a worksheet within my workbook. I would rather have a input screen that would give him a choice.
View 3 Replies
View Related
Apr 27, 2012
I've been trying to modify this code in several ways but I can't figure it out, I'd like to
a) use different sections for the email generation where the email address/surname/forename/location are in my user database
b) have a popup screen appear for the body of the email text, I don't like the idea of my secretary having to go into the code to change the text
c) another popup box requesting an email identifier that will set a 'flag' in the Email Information column to show they've had that email and (this may be ambitious) to record the body of the email text somewhere that I can review so there's a record of what's been sent out
This is the code
Dim oXlWkBk As Excel.Workbook ' Excel Work Book Object
Dim oOLApp As Outlook.Application
Dim oOLMail As MailItem
Dim lRow As Long
Dim olMailItem
[code]....
View 3 Replies
View Related
Jul 11, 2012
I 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 Related
Jul 22, 2013
An 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 Related
Jun 21, 2006
is it possible to control a device through GPIB interface using excel VBA?
View 4 Replies
View Related
Apr 23, 2014
Just striving to have this kind of "picture arrangement's macro" to work, macro that whenever you choose a certain cell and operate it on hotkey it'll:
1. Create a new folder named "PicturesAuto" beside the excel file (wherever the excel file located).
2. Inside folder "picturesAuto it'll create a new subfolder named by the same name of the sheet related to that cell you operated the macro upon. (let's say this cell placed on sheet named: "happysheet"- that'll be the name of the subfolder created.)
3. Inside the subfolder "happysheet" it'll create a new subfolder named by the text of the cell you activated the macro upon (let's say you initially operated the macro on B5 cell -which has the text "montana" so it'll create "montana" subfolder also inside subfolder "happysheet")
4. Lastly instead of having only the text "montana" in "B5 cell" like we had initially before activating the macro, the macro'll also add to that text "montana" the link to "montana" folder (path of excel file->picturesAuto->happysheet->montana)
The way I'm planning to use this is to add a lot of HR pictures inside those folders created by the macro that related to that specific sheet, and that specific cell I've activated the macro upon. Plus just having those pictures so organized is just outstanding in every scale, and the excel file will keep running smoothly without any resource consuming overload.
View 7 Replies
View Related
Jul 29, 2014
I am using Microsoft Excel 2010 and Microsoft VBA 7.0 on my system. I would like to eventually create a PowerPoint and insert charts generated in the Excel workbook. In the meantime, I cannot get the basic PowerPoint created.
The line in red is highlighted blue when the compile error "User-defined type not defined" message box appears.
Public Sub TryAgain() Dim myPowerPoint As PowerPoint.Application
'
' do nothing for now
'End Sub
I have set the references such that Microsoft Project 14.0 Object Library is indeed checked. The Excel file only contains this code in a module. All sheets are blank. Nothing else is written yet.
View 1 Replies
View Related
Aug 22, 2013
coding mandatory field in Excel 2010.
This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it
Sub NextInvoice()
Range("J2").Value = Range("J2").Value + 1
Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook
[code].....
I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.
View 1 Replies
View Related
Jun 14, 2014
I use the vba to open excel file. like workbooks.open filename:=abc.xlsm but I found that if the file is opened by other. Excel still open the file but it will not notify user that it is opened as read only.
Would you add more line to notify user if the file is opened by other?
View 2 Replies
View Related
Oct 3, 2008
I have a template excel file that is loaded with macros. When the file is opened, a userform opens and the user makes several different selections. Based on the selections, the excel file is populated with information and pictures. I want to make sure that the user does not accidentally save over the template file when he/she is finished with the document.
My original solution was: when the user opened the excel file, it would automatically save under a different name in a temporary location. This would stop the user from being able to overwrite the source file. The users were not happy because the excel file is very large and takes a long time to save.
on another method to stop the user from overwriting the source file? using code? or another method?
View 9 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Feb 7, 2008
I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.
The daily file that i use has info like date, truck number, delivery stops, weight.
the database file has the similar headings.
at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.
This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine
View 9 Replies
View Related
Jun 17, 2009
I have tried to create a macro using the record function to copy and paste the user name from Tools/Options/General. The problem is that the macro does not record the copy function and reads like this
View 2 Replies
View Related
Jan 26, 2008
Below 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
View 4 Replies
View Related
Jan 8, 2009
I'm trying to create a formula similar to this: =sum(B7:B & 8*A2+3). What I want the formula to do is start in B7 and go to B###. The cell number is based on user input. So if the user enters "24" in cell A2, then 8*24 = 192, 192 + 3 = 195, so the formula would be: =sum(B7:B195). Can I do this in a cell, or do I need to run it in a macro?
View 2 Replies
View Related
Sep 29, 2009
this is my first attemp at doing a "userform". I am looking for some help in creating a user form that enables users to choose items from drop down boxes, which shows next level drop down list items, then down to final drop down list with information based on first two choices. I've attached the sample file for reference.
View 3 Replies
View Related
Sep 4, 2008
Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?
For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.
View 9 Replies
View Related
Mar 16, 2004
Is there any way of creating a log that logs the last user that saved the workbook including the date and time. I would like the log to be a sheet hidden in the workbook. Not many people will be saving the workbook.
View 9 Replies
View Related
Feb 19, 2008
I often need to retrive info from SAP and fill in an excel document with it. Is there a book or a web page where I can find macros that will help me do that?
View 9 Replies
View Related
Jan 1, 2008
I'm trying to write a macro in Excel to interface with "Attachmate".
My problem is when I do a "CreateObject" I receive error for active x can not create object.
I looked in the Excel Ref Lib for either "Attachmate" or "Extra". and could not find them.
Does any one know for sure if Excel can work with "AttachMate" if so can you provide directions.
I'm using Office 2003 Pro, Windows XP.
As a side note I've read of "AttachMate" can be coded to work with "Excel", but so far not the other way around.
View 9 Replies
View Related
Nov 20, 2007
I have a workbook were a user will write down some info. Then I need to copy that data to a second workbook located anywere in the Hard drive. i used the function "GetOpenFilename" or the "Application.dialogs(xlopenfile) to ask the user where is the file (and open it). Now I need to get the name of that file. How can I get it?(the name of the file the user selected to open). P.S. I saw there is somewhere a "Print" function to copy data from a workbook to another closed workbook. I was planning to have both workbooks open and then just use several lines like:
View 4 Replies
View Related
May 13, 2004
how to get informazion with VBA code about the name of the user who opened already a workbook on the network.
Usually if somebody wants to open a file that is opened by another user, office applications give informations that the file is open and about the name who opened that file.
Is it possible to get this information with VBA code, too?
View 9 Replies
View Related
Oct 17, 2006
In order to beutify my prog's interface, i would like to open the Excel file(active VBA) with the worksheet in the minimized background or hide, i.e. only the Userform or a Dialog window, from the right beginning on! as if it was a standalone VB main interface.
View 3 Replies
View Related
May 29, 2008
I have one workbook with two worksheets (Jan-JUly and Aug-Dec). I am using a userform to add data into worksheet ("Jan-July"). But I do not have any clue - How do I update the other worksheet (same data like name,join year etc)worksheet("Aug-Dec") at the same time. I used two worksheets because worksheet doesn't support 370 columns, and to make my life easy. UserForm add data into Column A to M (worksheets Jan-July), rest is done manually. I have also attached the file.
Private Sub dataAddButton_Click()
Dim dataCheck, eMsg As String
Dim strLastRow As Integer
Dim x As Double
strLastRow = xlLastRow("Jan-July")
'get Last Row
With dataInput
'dataInput name of userform
If (.fNameBox.Value <> vbNullString And .fNameBox.Value <> vbNullString And .joinYearBox.Value <> vbNullString) Then
'textbox validation value can not be empty......................
View 2 Replies
View Related
Apr 21, 2014
I'm closing in on the final stage of a large project and I've run into a road block. The following line of code is preventing my users from being able to select specific page ranges when using the Print Entire Workbook option in the print menu.
[Code] .....
I've attached the latest version of my form (with significant support by jaslake) to explain what I'm attempting here. The attached form has several macros that activate upon open; including a user form. The password for the workbook/worksheets/VBE is "j".
Attached File : HCT-RCS ITP V1.89.xlsm
View 14 Replies
View Related
Jun 4, 2006
I am working on a workbook that contains drop-downs in various cells. The source for these drop downs are several Named Ranges on a tab named 'Drop Downs'.
I am locking the whole spreadsheet down so that people are not messing with the format, etc. (which is the current problem). So I don't allow people to add/delete rows, etc.
I am trying to think of a good interface to allow users to modify the entries in each drop down list (i.e. they may need to add a person to the "Resources" Range which feeds into a drop-down box elsewhere).
So, people should be able to add/delete items. I was thinking I would put a little 'X' next to each entry, such that when pressed - it would delete the corresponding entry. (Using the Assign Macro on a Text Box). However, I have to assign each 'X' a method to call - and I can't pass in an argument - so if I assign them all the same macro name - I won't be able to figure out which one was pushed. And the number of 'X' will be dynamic, so I can't hard code a bunch of functions. I don't really have a good idea for allowing additions yet (other than a button that prompts for the name and then adds it in).
I guess I could just use a Form that allows editing of the data (that would be easy and powerful to do), but I don't really want to do that - I want it to be editable within the excel spreadsheet itself.
View 6 Replies
View Related
Jul 18, 2007
I'm writing a program on VBA for Excel and I've succeeded in just displaying the Userform when opening the file. I have set the ActiveWindow.Visible option to False and the Application.Visible one to false as well so that i only have the user form displayed.
However, if i try to open an excel file via "My Computer", the file doesn't open. I also noticed that when I was leaving the application.visible option to True, i couldn't access the application while the userform was running. I think i need to do something with an event, but i can't find the event corresponding to opening a file oustide Excel.
View 2 Replies
View Related
Mar 2, 2010
I've got numerous vba-coded spreadsheets in use with lots of different users, and have found that some users try to hide errors of their own making by blaming the spreadsheets (I know - what is the world coming to!)
I've decided to add a script which creates a log file each time one of my spreadsheets is used - opening a log which relates to the user's spreadsheet and recording username, date/time, path & file, and the vba being used.
I'll probably add an "on error" script to save copies of all open workbooks too.
The issue I have at the moment is how to record the name of the macro being run?
Here's my code so far: ....
View 9 Replies
View Related
Sep 18, 2006
I built a web form interface which I use to adjust orders on a web-based system our company uses.
The code I have looks at a sheet, checks if the line needs adjusted, makes adjustments as needed, and then clicks a box for lines needing ordered.
Here is the code I use:
Sub Adjustorder()
Application.ScreenUpdating = False
Dim ie As Object
t = ThisWorkbook.Sheets("Adjust").Range("C3").Value 'checks for valid orders to adjust
If t = 0 Then MsgBox ("No Skus to add"): Exit Sub
Set ie = CreateObject("InternetExplorer.Application")
With ie
This all works fine, but the amount of lines can be anywhere from 1 to 11,000. The fewer the amount of lines needed to loop through, the faster it does each line. With 11,000 lines it took nearly 1 second per line. With 200 lines it took 2 seconds for all of them.
View 9 Replies
View Related
