Times In Forms
Mar 16, 2009I have a table in Excel which holds Names, StaffID & Times for working (Start & Finish Times)
When I open the form, it shows the times as a Number how can I get them to show as a Time (hh:mm)
ADVERTISEMENT
Macro Loop – Find Specific Times, Extract Date, Average Values Of Times
Feb 23, 2010In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
Excel Formula To Calculate Difference In Times But Only Between Specific Times
Apr 8, 2014I'm looking for a formula that will calculate the difference in times between specific times while working with a 24 hr clock. Please see details below:
E3 provides the start time of 4:00
H3 provides an end time of 15:30
If an employee works betwen the hours of 0:00 (midnight) to 5:59, this is considered DIFF hours and is therefore the number I am seaking. So for the data noted above, the total DIFF hours worked is 2 hours.
Convert Start Times And End Times And Minus
Mar 7, 2009I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
Tab Order In Forms
Jun 11, 2008I would have like a chance to be able to remove the apparent potenitial dangerous problem from the thread below and not close it after 10 mins. I'm not on the site permenantly and keep dipping back in eveynow and again. I dont know what the issue is as when I open the file up I dont get any error messages. I have tried this on my machine using Office 2003 and Vista also another PC using Office 2003 and XP
[url]
The only error I get is a message saying Disk or Network error, when testing it on the XP Machine this is because doesnt have access to the Database it sends the data to.
VBA Report With Forms
Dec 5, 2009sometimes pivot tables won't give me the details I want the way I want. any time my boss asks me for a monthly report I have to do everything manually from the pivot table and insert the data on another table where I put the filteres data. I want to be able to get a monthly sales report per store that will populate itself in a daily basis, I also want to be able to select a store, style, size and date from a form in Excel and see the report in a sheet. Aproblem that I have found in order to use the size as a parameter is that the size comes in the same line as the item description and do not know how to make excel to check just for the size in the description field.
The attached file contains sales data for a better understanding of my case.
Combobox In Forms
Oct 13, 2008I have created a form which is called up via a macro and then shows a combobox which contains the names of the worksheets in the workbook.
I am trying to enable the user to select one of the worksheet names from the combobox and then store that name (the book does not have fixed sheet names) and continue with the original macro using the stored name.
I just can't figure out how to use the selected name in the original macro.
Numbering In Forms
Dec 29, 2008I have created a form to input parking ticket data to a spreadsheet, it all works exactly as i want it to, but i really need it to tell me the next available number or empty line, so i can use that for filing and audit purposes, ideally i would like it to do sequential numbering, but i've been looking for weeks and cant find a soloution, i have basic knowledge of VBA and i'm really struggling with this,
View 9 Replies View RelatedListbox And Textbox On Different Forms?
May 13, 2014I have a listbox on userform1 with multiselect and i am trying to populate the selection into textbox1 on userform2.
View 6 Replies View RelatedCopy User Forms
Nov 7, 2008So I have a work book with a number of user forms. One form I have completed has several navigation buttons (previous, save, next......) a couple of drop downs and a "tabbed" (pages) area with a text box on each. I think I finally have it looking (and working) the way I want. The issue is I need 12 more that are Identical (different text - but layout and code is the same) To make them look and work the same how can I copy the one I have correct? Is Export/Import the way to go? Does that bring all the code?
View 3 Replies View RelatedFORMAT In VBA On User Forms
Nov 18, 2008Excel 2003
VBA on user forms
Calling Forms Using Variables
Nov 9, 2009I would like to know if it is possible to display a form using a variable containing the form name rather than the form name itself, I have tried everything I can think of and cannot make it work. I know I can use CASE SELECT but I have a large number of forms involved and would like to use a variable name if possible.
View 4 Replies View RelatedCopy Forms Checkboxes
Aug 25, 2012I have checkboxes in N85:N100 and O85:O100. When I use the following code, it gives the same link in N85 and O85. How can I put a specific rannge so that it will only do N85: N100 and then do O85:O100.
Sub change_forms_checkbox_links()
Dim mychkbox As CheckBox
Dim wks As Worksheet
Set wks = ActiveSheet
[Code]...
User Forms / Menus
Nov 21, 2006I am trying to graduate out of the crayon age of menus on a worksheet, to using a User Form Menu. Problem is, I don't understand how they work. I've created a test program using a simple form with an option button, a combo button, and a command button. The goal is if the option button is true, it places the color selected in the combo button on the worksheet. Here is my code, what am I missing to make it work?
Corrected code from my original request
Module 1
Public ClrCd As Integer
Sub OpenForm1()
UsrFrm1.Show
End Sub
Dependent Drop Down Forms
Aug 12, 2007I am trying to put together two combo boxes, Combo1 and Combo2, the content of Combo2 will depend on the selection made in Combo1, for example Combo1 will have 10 items, once an item is selected Combo2 will have different sub selections relating to that item. I know you can do this via Validation, but the problem with that I have about 4000 options in the combo1 and about 60000 in the second, here is the structure of my data:
First Option /Second Option
Option1_____1.1
Option1_____ 1.2
Option1_____ 1.3
Option2_____ 2.1
Option2_____ 2.2
Option2_____ 2.3
Option3_____ 3.1
Option3_____ 3.2
Option3_____ 3.3
My ultimate final result should look like this, two drop down menus once Option1 is selected second drop down form should display these options:
Option1 /_____ 1.1
____________ 1.2
____________ 1.3
see attachment for complete clarity
Forms - Scrollbars & Multipages
May 8, 20081) I have a scrollbar that gets set to UserForm1 height.... yet, if the form is taller than the page, it won't let me scroll down past what I can see on my computer screen... what would be the cause of that? I get a blinking black light on the scroll bar as well so there must be something I need to fix with that
2) In UserForm1, I have a multipage form with a page called Accounts... I am adding check boxes to it with code... right now it is adding it to the far left in UserForm1... what is the syntax to run my check box loop to populate the Accounts page in the Multipage form?
Buttons Code For Forms
Nov 15, 2008I've been creating forms/questionnaires, customer satisfaction type tings with Excel after I discovered that the previous ones were done on paper and it’s now my job to crunch the numbers for a report.
While I've been off learning how to do this in a suitable way for the office I work at, I've been neglecting the stack of papers from previous customer satisfaction surveys that are building up on my desk, about 200
So now rather than do what I think everyone else would do and make up the numbers. I thought I would just make a excel book with buttons for all the possible options on the survey and click them as I look through the papers.
So I need to learn how to make a button that, when clicked it would add a number to a cell. So if I clicked it 10 times, that cell would show 10.
so I can make the form with the buttons, what code/formula will I need to assign to the button to make it +1 to say, sheet2 A1?
Exporting Forms To Word
Mar 16, 2009I have spent a long while trying to figure out what i'm sure is a very simple problem. I have searched the archives and found nothing to specifically address my question.
Basically I am trying to export the data entered into an Excel form into the fields of a Word document template, so that i can print it according to a particular design.
I have managed to write the code successfully enough that the data is transferrred across to the right fields in Word, but I can't seem to work out how to transfer the subsequent rows. It is currently only exporting the second row (after the header).
Could anyone possibly look at my code and tell me how i can export the new values every time they are entered in the form and not just the first line?
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub Save1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("RA")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
Forms Toolbar - Textbox
Sep 15, 2009I'm using forms toolbox to add textboxes on an user input sheet for my workbook.
There would be 3 textboxes included in the group box added to the user input sheet.
I'm struggling with the code to accomplish the following:
Textbox1 - is a % input. I would like the input to be formatted to 2 percentage points. In other words, if someone enters 2, it will show as 2.00% and I want the 2.00% to be recognized as such in the spreadsheet calculations. In other words, the input is used in the calculations in another sheet.
Code thus far (and I may be offbase on this):
Private Sub TextBox1_Change()
Sheets("Sheet2").Range("rate").Value = TextBox1.Value
End Sub
Problem - the cursor is not moving and the result of the input is not showing. I would like the cursor to move from textbox1 to textbox2 to textbox3 by tab or enter button.
Textbox2 - is decimal input. I would like the input 5, it will formatted to show as 5.0. This input is used in the calculations in another sheet.
Private Sub TextBox2_Change()
Sheets("Sheet2").Range("multiple").Value = TextBox1.Value
End Sub
Textbox3 - is a result box based on the inputs from textbox1 and textbox2. I would like the result to be shown to the user in this textbox3 and for it to be greyed out and not to accept any inputs from the user.
That is, it is not an input box but rather a results box.
Auto-numbering The Forms
Jun 1, 2006I have created a bill of lading form that we use in shipping. Each time they need a new form, they copy the "blank" form. Is there any way to have these forms "auto-number"?
View 7 Replies View RelatedPassing Variables Between Forms
Jun 28, 2006Is it possible to pass a variable between forms? For example, I have a textbox on a form that takes a date from a user. Next to the text box is a button that calls another form that is a selectable calender with each of the dates on the calender as buttons. If a date button is pressed a variable is created which I want to pass back to the first form and place it into the textbox.
View 4 Replies View RelatedForms Toolbar - ComboBox
Feb 24, 2007I’m trying to do a pull down with scrolling ability in a cell that references another worksheet. I want to look like the one that occurs when you use “Data Validation”. I’ve seen this in another workbook and it does not look like the used VBA.
View 4 Replies View RelatedRefer To Objects In Other Forms
Mar 13, 2007I'm trying to refer to a togglebutton in a form from a command button in a different form.
Right now I have
Private Sub CommandButton1_Click()
If ToggleButton1.Value = True Then
Pickers.Hide
EndRun.Show
Else
Pickers.Hide
Runinfo1.Show
End If
End Sub
but I assume I have to preceed the Togglebutton1 with the name of the form that it is in but I am not sure how.
I don't even know if the above code will work as obviously I haven't got that far yet...it may be completely off...
Button In The Sheet To Run Forms
Mar 20, 2007to create button that run the form!
i did try but the button did not run the form!
Placement Of Forms Listbox
Apr 23, 2007I have a list of People on one sheet and a list of Shifts on another. Some people have Qualifications (CPR, electirician, Non-Violence trained, etc.) and some shifts have Requirements. I would like to provide a (non-required) validation list for these entering these values. If the user enters a new requirement or qualification.
An explicit Validation list is too long. I don't want to use cells to hold a list that Validation can read from. So, my current plan is to a Selection_Change event to create/destroy a form ListBox, which will be populated by my VB code. How to place that is presenting some challanges. How to turn the user's multiply selected input into a comma delimited string is the easier of my two main challanges.
So, my concice question is when the user selects a single cell, how do I create a listbox that is possitioned over that selected cell? My non-concice question is: Is there a better way to go about this?
Data Entry Forms
Jun 4, 2007Need to create a Data Entry Form in excel?. I would like to insert data to an excel database using a form.
View 4 Replies View RelatedValue From Forms Combo Box Control
Mar 26, 2008I have a (form control) combo box in my worksheet. Inside the combo box are several different ways a data set might be sorted. I would like to refer to the current selection of the combo box in my VBA code because I have a macro which executes differently based on the selection in the combo box.
I have found this piece of code which I know executes when the combo box selection is changed.
Sub DropDown1_Change()
However, I still do not know how to refer to the value of the current selection.
Below was my attempt to assign the current selection to a name, ComboValue, that I created:
Sub DropDown1_Change()
Names("ComboValue").value = ComboBox1
End Sub
Strangely, this code executes once, but removes the name I created. It then returns an error on subsequent runs.
Delete Forms CommandButton On Worksheet
Jun 5, 2006How do I delete a command button I've entered on my Worksheetright-clicking does nothing;control-key+right-click does nothing alt+ctl+right-clicking does nothingWhat's the secret?
View 8 Replies View RelatedForms Button Background Color
Aug 23, 2009Can the backround color on a forms toolbar button be changed or must I use a control toolbar button if I want the button in color? I would prefer using the forms button and have tried customizing (tools/customize/forms) then right clicking on the forms button icon and selecting “Edit button image”, clicked on the desired color and clicked ok.
View 2 Replies View RelatedAuto Numbering Of Printed Forms
Feb 6, 2014Many people use Excel to generate forms that will be printed off and carried around on a sheet of paper, because the boxes and lines are nice and square, and straight, Word is not so co-operative
There have been lots of queries how to auto-number such forms, so that you can later file or issue them in a particular order, or use the numbers later as a reference doing other stuff.
Here's what I want to do:
I run a limousine service taking clients from places like hotels to various destinations
I have a little travel voucher form, to be filled by hand, 3 copies to a sheet, so we are gonna print them, and cut the page into 3 lil vouchers. Like printing lottery or competition tickets. The vouchers have artwork to suit the place requesting my service.
They are NOT invoices, to be opened, numbered, worked on, and THEN printed
Cell G2 will start autonumbering at 1001, Cell G18 would be 1002, Cell G34 would be 1003, then when the second page prints, we get 1004, 1005, and 1006 respectively. Each sheet has to be individually cut and stacked
Alternately, we decide a quantity to print (50) and start G18 at say 1050 and G34 at 1101. This would be better, because when cutting the paper up with a guillotine, the first 50 vouchers are already in number order, the 2nd 50 get stacked under them, and finally the 3rd 50 below that, so now my stack of vouchers runs from 1001 to 1150 nice and easy and neat
A drawback with this is that I have to do specific print runs and somewhere in the document, specify the starting number for G2
I have heard of a method to print, say 20 forms, starting say, 1001, ending 1020 and a text file saves the last number used, so the next print run picks up from there, at 1021, can that method be combined in? I'm working in a small office that needs these vouchers but we dont wanna have a commercial printshop tool up (big $) to do small print runs
I can also do the vouchers for other suppliers to use with different artwork