My manager wants me to create a userform that will populate values from access database . I have done that and now he wants it to run always for the whole day everyday and the values should get updated automatically in the userform. There should be a timer event set that will run the userform automatically after few seconds.
View 3 RepliesI have a macro created that at the mo is assigned to a button. But I would like the macro to run as soon as the spreadsheet is opened.
View 9 Replies View RelatedI have written some code intended to automatically clear a cell when data appears in it from an external source. In one spreadsheet, it seems to work exactly as expected, ie when the cell is polulated, it immediately clears the contents. In another spreadsheet, with exactly the same code, it will not automatically clear. If I put it in debug mode and step through it line by line, then it does do as expected but otherwise it won't.
View 9 Replies View RelatedIs it possible to run the following macro automatically using VBA when cells B2 and C2 equals "Yes"? I don't want any manual intervention for this to happen.
Sub clearcells()
'
' clearcells Macro
' Macro recorded 14/10/2009
'
Selection.ClearContents
Range("F3").Select
Selection.ClearContents
Range("G3").Select
Selection.ClearContents
Range("H3").Select
Selection.ClearContents
Range("H4").Select
End Sub
I am using a macro that automatically updates data obtained through a DDE link at 5 second intervals. My code is based off of the article "Automatically Run Macros at a Set Time or Date" found on the Ozgrid FAQs found here: http://www.ozgrid.com/Excel/run-macro-on-time.htm
However, I have this macro execute when I activate a Form Button, not when the workbook opens. So far, this macro works perfectly, and I have no problems. Although, I'd also like to include a Form button to STOP the macro from automatically running if the user chose to do so. How exactly would I go about making this STOP button?
I ran into what looks like to be a syntax error.
Application.Run " Name with Spaces!Run_frmMacroList"
does not work BUT.... when i take the same file and "save as" Book1
Application.Run "Book1!Run_frmMacroList"
works perfectly fine.
I know this may have been answered before but I didn't really understand those answers and my scenario is slightly different. I have a UserForm that I am asking people to enter data into. One part of the UserForm asks for a percentage spread across an age range. For example for the age ranges 16-20, 21-25, 26-30 (etc) whats the percentage of smokers. This will obviously then add up to 100% which I want to put in a ' total' box at the end of the line of input boxes. I want this box to be updated whenever someone enters a percentage into the boxs but I can't seem to work out how to do it. That's basically what I'm asking help for on. Its easy enough when doing it on the spreadsheets themselves with the SUM function but how can I do it on a UserForm. Does it need some complex VB coding?
View 7 Replies View RelatedI am using Excel 2011 for Mac.
I am creating a workbook with multiple worksheets, one overview sheet and then one sheet for each month of the year.
In the monthly sheets I have a Category column for which I have created a drop-down menu of expense categories. Then I have an Expense Amount column where I enter the amount spent.
The Category column will not necessarily remain sorted by category because I will be entering the expenses as they come up throughout the month so the categories will be all mixed up, unless I manually sort them.
What I want to do is somehow automatically calculate a running subtotal by Category (that will update with each new entry) and simultaneously automatically transfer the running subtotal for each Category to a specific cell on the overview sheet.
is there any way to work with excel workbook while in another excel workbook userform is running
View 1 Replies View RelatedI am copying web data into Excel and need a creative way to run a couple formatting macros on the data. I’m looking for the best way to initiate the macros. I cannot use command buttons because they’ll get deleting as a result of the line “DrawingObjects.Delete”.
I’d like to use a Useform with a couple buttons but am not sure how to have it automatically display when needed and hidden when not needed. I also prefer not using toolbars button unless they will only be displayed in that workbook and not any others.
I currently have a userform that runs a bunch of code for me. There are a lot of listboxes on the userform that have rowsources of various dynamic named ranges. I'm running into a problem when I have the used click the "Remove Item" button from the userform. Normally it is supposed to go back to the sourcerow and remove all of the cells that contain values that are selected in the listbox.
The code for removing the cells works just fine, however when it deletes a cell, for some reason the code executes a private sub that is in a different sheet. I can't figure out why and it's messing up my macro.
Here's the
FROM USERFORM:
Private Sub cmdAIRemove_Click()
For i = 0 To lbAISummary.ListCount - 1
If lbAISummary.Selected(i) = True Then
For Each cItem In Worksheets("Lists").Range("YourAI")
If lbAISummary.List(i) = cItem.Value Then
cItem.Delete (xlUp) '<- right after this line it jumps to NWSMonth_Change
i = i - 1
End If
Next cItem
End If
Next i
End sub...
Here is the code for my user form.
Private Sub UserForm_Initialize()
TextBoxDate.Value = Now
TextBoxDate = Format(TextBoxDate.Value, "dd mmmm yyyy hh:mm:ss")
End Sub
This is a form to have employees punch in/out. The form is designed to be running all the time. The TextboxDate is disableed so the user can not change it.
I would like to know if it is possible to get the time to be displayed on the form constantly? Right now it only updates when the OK button is pressed on the form.
I am trying to sum a dynamic range of values, clear the range (ie. clear the numbers on the spreadsheet), then place the sum of all the numbers in the last cell in the user selected range. (essentially adding all values selected, placing final sum in the last row of the range, and leaving no trace of performing the routine)
I want the range to be selected during the userform running (using showmodal = false). I can sum the selection and write the value to a cell, but I can NOT write the cell to the last value in the selection.
I'm using a UserForm to inform the user that the program is still running. Unfortunately, when I do: UserForm1.Show the program hangs there until I hit the Ok button. How can I make the program run as the box is shown.
View 2 Replies View RelatedI have to append the cell value with a user input string in to the same cell.
i.e, if the user types "Issue" in the cells ranging C1:C200, I would like to make a mandatory issue description to be entered by the user. So a user form with text box and command button was introduced which should append the cell value with the user input in the text box.
The issue that I have is with the cell selection within the code for the form.
ActiveCell.FormulaR1C1 = "Issue (" + TextBox1.Text + ")"
For example if the user tabs out from cell C3, the output from the userform will be written to D3 ? or if the user hits Enter key to come out, the output will be written to C4 and so on.
How can I make ure the output is going to the same cell?
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Application.Intersect(Target, Range("$C$1:$C$200")) = "Issue" Then
Dim MyForm As New UserForm1
MyForm.Show
Else
Exit Sub
End If
End Sub
'Code for the userform is below:
Private Sub CommandButton1_Click()
If TextBox1.Text = "" Then
' the user input is mandatory
MsgBox "Issue Details is mandatory", vbCritical, "Mandatory Field"
TextBox1.SetFocus
Else
' append the cell value with user input in the text box
'****issue is here in the next line
ActiveCell.FormulaR1C1 = "Issue (" + TextBox1.Text + ")"
Hide
End If
End Sub
I am trying to get running totals in three TextBoxes on a UserForm. The UserForm is to enter one item from a receipt which can have many items. The running total is for the number of items on the receipt, amount of discounts (coupons), amount of sales tax and the grand total. A small sample is attached
View 4 Replies View Relatedhow to run a userform with a macro that opens the visual basic editor and runs the userform1 in it; rather than using userform1.show.
View 9 Replies View RelatedI have a User Form which i want to use in such a way that editing the worksheet directly is not disabled while the userform is running. I would also like to use the right button mouse menu(cut, cpy,paste) while i am using the user form. I am using two Monitors with my PC and i want to work with User Form on one monitor being able to edit worksheet without having to close my User Form.
View 2 Replies View RelatedI have this VBA set for a workbook_open event:
Public Sub Workbook_Open()
PromotionOrNewHireForm.Show
Sheet1.Select
End Sub
...which works, but I would prefer to have the form hide (or unload, exit, etc.) once the toggle button in that userform is first selected. I've tried a few things but none of them have worked. BTW, the userform has two toggle buttons, which run the following:
Private Sub ToggleButton1_Click()
Worksheets("Sheet1").Range("A4").Value = "Promotion"
End Sub
Private Sub ToggleButton2_Click()
Worksheets("Sheet1").Range("A4").Value = "New Hire"
End Sub
Also, once it closes, I'd like it to move on to the next macro depending on whether "Promotion" or "New Hire" is selected. Not sure if a workbook_change event is the best way to handle that.
I have created a software on a userform and have copied contents of this userform to worksheet cells. However when I run it for the first time everything works and all contents goes in C4 - C8. However when I run it for the second time the contents in C4 - C8 deletes itself and new data appear i.e. the cells are overwritten.
In cell C3 i have the date and I have managed to put this date in the next empty column everytime the software is run. I do not understand why the dates can go to the next empty column without it being over written but cells C4 - C8 cant!
Private Sub txttodaysdate_change()
txttodaysdate = Format(Now, "mmm/d/yy")
End Sub
when i use this code i wnat the date to automatically appear in the text box but it doesn't I have type something into the textbox then the current date appears,.
I have a userform that pops up upon opening a file. The user form has
5 text boxes names Zero, Two, Four, Seven and Nine. I would like each of these text boxes to automatically populate with the values last used. The values last used are stored in Worksheet "Index Settings" in Cells "C3:C7".
When I click the command button that is supposed to close the userfrom, it closes it but it re-appears again. It closes completely when I click the "Cancel" command button again. It seems to reload the userform when the command button is initially clicked.
Private Sub CommandButton1_Click()
Unload UserForm2
End Sub
I have table in excel sheet and i want Create an Excel User Form display automatically to this table, how?
View 3 Replies View RelatedIs is possible to automatically place a toggle button on a user form by entering data in a cell?
In otherwords, if I type a number in cell A1, can that number be automatically put on the toggle button and placed on active userform?
I have some Worksheet_SelectionChange code that automatically calls a Userform when a cell is selected.
What I want to do is retrieve the data from the cell where the Userform is called from and enter it into a textbox on the Userform.
There are 3 other columns to the right of the cell that is selected.
If there is data populated there, I want this to be copied to the appropriate textboxes on the Userform too.
I have created an Excel spreadsheet that collects info from a userform; we are using this as a issue log.
However, at the same time, upon completion of the log, the engineer then needs to fill out the essentially the same information in a Word form for sending off to the customer. I was thinking that perhaps this could be done by using a mail merge but from past experience this generally isn't very user friendly or quick.
Is it possible to automate this somehow using VBA such that when Enter is clicked on the form, not only does it fill in the next available line in the log spreadsheet but it also opens Word and populates the required fields with this same info?
I have the following code in a simple userform containing only a label:
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Sub UserForm_Activate()
DoEvents
Sleep 4000
Unload Me
End Sub
When I invoke this as a single macro in a blank workbook and immediately change focus by pressing ALT+TAB to enter NotePad the userform is shown and exits normally after 4 seconds.
However, when I issue this code from within a larger macro, and hit ALT+TAB to Notepad, the excel application hangs on the displayed userform until I click back into Excel.
If required, I can happily send you the code as an attachment.
I've got a User Form that works perfectly. It's activated on Chart Month sheet, you enter data into it and it switches to Comments sheet and copies the data, before returning back to the Chart Month sheet.
I have now added the same functionality to Chart Week sheet. Both Chart sheets use the same comments data, so it doesn't matter if you run the User Form from Chart Month or Chart Week. However, when I run the User Form from Chart Week then it switches to the Chart Month sheet once it copies the data, as I have Chart Month in the code. How do I get it to return to the sheet that it was originally on ie either Chart Week or Chart Month?
Code:
Private Sub CommandButton1_Click()
Dim emptyRow As Long
'Make Sheet30 Active
Sheets("Comments").Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("D:D")) + 1
[Code] .........
I have put together a booking spreadsheet which uses a UserForm. see attached worksheet. When you start the UserForm and select a name, date, number of nights, and then 'Add', the data is added to the worksheet.
1. My first problems is that when I select the 'Cancel' button on the Userform, I would like the spreadsheet to automatically save under C:My Documents. And also save the file as 'Marketing_DDMMYYYY.xls' as todays date.
2. My second problem is that the 'Leaving Data' column sometimes shows the leaving date in American format (MM/DD/YYYY), and sometimes English format (DD/MM/YYYY).
How can I make this always use English format?