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.
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.
Problem: I have textbox entries that are part of a Userform that opens using a macro on a speadsheet button. Once this form is open, I can no longer actively work in the spreadsheets.
Need: I need a way to minimize or "put on hold" the Userform so that I can freely move around in the spreadsheet. This could be in the form of a button on the Userform. Then, I need a way to bring back this userform to the point I was at before being put on hold so that I can continue to input entries into the textboxes (again, a buttton that could bring it back). There should be a way to toggle between both worlds.
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......................
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".
I am trying to create a way to protect and unprotect all sheets and the worbook by means of a Userform pop up interface.
I have managed to create the Userform but what the code will look like and where to place the VBA code that will make it work, Also i would like to automatically save the sheet/s as i switch between them. so that any updates made to the sheet will be automatically saved when i move to the next sheet and also when i protect or close the workbook.
How do you make it so when a user form is open you can still interface with the backround (the Spreadsheet) For example like the Microsoft Tool Boxes.
Right now, when you use the userform you cn only select things on the userform. I want to use the userform as "quick view" of the spreadsheets main Values.
When I first open a workbook most of the functions don't work. for example the vertical scroll bar does not move. When I close the workbook and then reopen it for the second time it works fine.
Code: Sub Macro1() ' Sheets("Log").Select Range("F1").Copy For Each wb In Workbooks If wb.Name Like "Test Book*" Then wb.Activate Sheets("Data").Select Range("O2").Select ActiveSheet.Paste
[code].....
can't get it to work properly...Basically I want the code to copy the date in cell F1 of the 'Log' sheet in Workbook 'Main' - then goto an open Workbook called 'Test Book' (note: this Test Book is a partial string name used hence the other code around it) and paste the copied date into cell O2 in sheet 'Data'
Using this pasted date in cell O2 carry out the required filter function.
The copy/paste of the date from my Workbook Main to Test Book isn't working so the code then shows error when trying to filter the data using the pasted date.
I currently use a spreadsheet to track my work and I would like help automating some of the functions. I don't want to use macros if at all possible. I have some formulas but it doesn't do enough. what i do is audit other peoples work and send out error notifications when needed. i use the spreadsheet to help me tack the status of the error notifications. when I send out an error notification the auditee has up to 3 chances to rebut the error so i need to keep track of where the follow up lies and when the error is finally accepted or removed (process completed).
I would like column U to tell me where we are in the process... 1.notification sent (by the auditor) 2.reply received (either accepted or 1st level removal request by the auditee) 3a. if error accepted- process complete 3b. if removal request-notifcation sent(by the auditor - error removed or error remains) 4.if still an error(following 3b) - reply received (either accepted or 2nd level removal request by the auditee) 5a. if error accepted- process complete 5b. notification sent (by the 2nd level reviewer - either error removed or error remains) 6.if still an error(following 5b) - reply received (either accepted or 3rd level removal request by the auditee) 7a. if error accepted- process complete 7b. notification sent (by the 3rd level reviewer - either error removed or error remains) & process complete (3rd reviewer decision is final)
I would like the V column to be the "date of last action" since the notification bounces between people, i need to know who is currently responsible for followup & how long they've been sitting on the work. i would like the date to populate based on the manually inputed fields where I input sent & received dates for each level of action.
the spreadsheet is too wide for the post so i will put it on a website... http://public.box.net/kingerick89367
the columns are all titled and all manually inputed except for column U which is the one that I want to autopopulate with the current status of the error notification. the formula is a IF statement but due to the 7 formula limitation It came out too simple. it's better than nothing but I want it to be more specific.
I am trying to simultaneously use two Excel workbooks that are open at the same time--one is calculations--other is data scenarios...one worksheet = one scenario: A macro uses "Set" to specify workbooks for "current_wb" and "wbkFrom". Code here
Set current_wb = ThisWorkbook Dat_Fil = Application. GetOpenFilename Workbooks.Open Dat_Fil Set wbkFrom = Application.ActiveWorkbook
NOTE-Workbook object variables are declared outside any macro, and as "public." Code here
Public current_wb As Workbook Public wbkFrom As Workbook
In a second macro I want to use the Current_wb and wbkFrom object variables I set in first macro, but get the following error: "object variable or With block variable not set" for any of the following statements in the second macro
I've declared the object variables as public in a different code module where there is no other code; I've tried declaring as public at the top of the code module that has my macros, but declared at top of code module and outside any macro; I've tried declaring the object variables as "static" variables (instead of "public") within the first macro. I can't figure this out.
What I would like to do is to be able to open a second workbook from a user form, preform some work on it then save and come back to the same place in the original user form.
So in steps:
1 : user clicks command button to open user form 2: user then clicks on command button on userform that opens 2nd workbook via a yes / no message box, but closes userform on 1st workbook (would be ideal if this could stay open, but hidden) 3: user then does work on 2nd workbook, 4: userform on 2nd workbook saves then activates the 1st workbook and reopens the userform
This is where no matter what I try I cant get the command button on the 1st userform to be clicked automatically so the yes / no message box appears.
This is part of the code in the 2nd sheet commandbutton that saves / closes / opens
Code: Unload Me ActiveWorkbook.save Windows("ABC.xlsm").Activate Sheets("Request Sheet").Activate Call Sheets("Request Sheet").ForceClickOnBouttonXYZ Call UserForm1.CommandButton6_Click 'this is where I cant get it to work!! Windows("xyz.xlsm").Close ABC is the 1st workbook xyz is the 2nd workbook
This is the code on the 1st workbook I use to call on the 2nd workbook
Code: Public Sub ForceClickOnBouttonXYZ() Call CommandButton1_Click End Sub
Created a new tab and menu buttons on the Ribbon in Excel in 3 separate .xltm files. Everything worked great through all of the development and testing, but when we went live we got reports of a bug. Here is what is happening...
User opens the template file and immediately saves it as a .xlsm file with a new name in a new location. The user then does various amounts of work in the file. When the user needs to use one of the tools on the custom tab, it is as if the buttons are inactive or not associated with any actions. The tab and buttons are all visible, the animation of the button being pressed works when any of the buttons are clicked, but nothing happens. The user can close and reopen the file and the ribbon buttons will work correctly, but not until they close and reopen.
Upon digging into the problem, we found that if the user clicks on the custom tab before they save the file, all of the buttons will work correctly after the save. But if the tab is not activated before the save, they dont work until a close and reopen.
The XML code is good because if I place it in a add on file (.xlam) and trigger it from the main file, there is no problem. But this is not a good solution since the add on file needs to be located in the same file that the main file is run from which can be any number of places.
I'm trying to return the selected items from a listbox to a range within a Worksheet when the user press the >> button as shown below. The point is i can't return ALL the selected items but only the first picked by the user.
Here is the code i'm using when button >> is pressed:
Code: Private Sub Inserir_Atributos_na_Analise_Click() Dim i As Long Set ws1 = Worksheets("ListBox")
I came across a userform which was perfect for my needs, as I only had to create additional fields, which I did and have attached. However, since I have more than 10 fields, I was getting an error with respect to the listbox function, where it stated that the list property could not be found. I gathered from the searches I did, that this is the max limit for this particular function. I have been unsuccessful in finding a solution, partly because I don't know very much and I'm not understanding how to incorporate some of these solutions into the vba code. I've created simple userforms before, but this one is a little more advanced than my current capabilities.
VB: Sub openfiles()Dim Path As String Dim ExcelFile As String ' Path = GetFolder("C:UsersKinteshDesktop") Path = "C:UsersKinteshDesktopVBA programmingMaps" ExcelFile = Dir(Path & "*.xls")
[Code] ....
NextCode: GetFolder = sitem Set fldr = Nothing End Function
My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.
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?
I have this formula =COUNTA($A:$A)<=4 that limits amount of cells that can be populated in column A, I use data validation with "Allow costume" option and using that formula. It works fine from worksheet it displays the message when the limit is reached but it doesn't work when data is inputed/populated from userfrom, it allows userform to put more entries than set limit 4 in this case.
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.
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.
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.
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