Is it possible to configure the workbook so the user can view the data in the worksheet from the form and I can hide the actual worksheet so that the data in the workshhet cannot be changed? Or maybe there is a better way all together??
I am attaching a copy of the worksheet.
Second, Is there a better way to input a code that would allow a search for duplicates in certain fields to be flagged (other than the color coding?)
I want to view my sheet2 using forms. For example I'll just click on the command button after that I can view my sheet2.
View 3 Replies View RelatedI have several files with form control buttons that automate functions but, on occasion, they get smaller relative to the worksheet they're in. At some point, they become unuasable unless they're manually resized.
View 8 Replies View RelatedI have a rather large sheet with lots of embedded form control buttons, each one with a document link. Is there a function (no VBA) to search for a specific form control button in excel? for example: i want to find the form control button which is linked to the "application" document.
View 8 Replies View RelatedI have made an excel sheet for multiple choice questions, in which person needs to click on submit button after completing with the test & pop up will say "Your score is__". Now, I want to want to add one more button named "Reset". I want below :
results:
1. Person should not be able to make any changes after clicking on "Submit" button.
2. If one tries to click on "Reset" button, it should ask for password.
3. After keying password for "Reset" button, all questions should be reset so that next person can give test.
I have attached an example excel sheet for reference : DoM1.xlsm
I am trying to get a form sent as an email by pressing he control button without having to cut and paste into an email sheet. I have tried with
[Code] .....
but the code I put in only opens a new email form, which requires cut and paste.
Is it possible to use a Form control which choose a folder/file in a specific location (Ie: Browse button)?
Currently I put the location details in a textbox.
So I have a sheet with a lot of formulas and form control buttons that have been assigned macros.
When I go to protect certain cells and lock them and the activate sheet protection, suddenly my form control buttons with the assigned macros don't work.
I still need to protect some cells with formulas and don't want to leave the whole worksheet unprotected, but in order to allow the form control button macros to run, it appears that's what I have to do....or, is there another way to do this?
Excel 2010. How/where to define userform object?
Button on worksheet has following code:
Sub Button1_Click()
Load UserForm1
UserForm1.Show
End Sub
Execution of this code generates following error:
Run time error 424
Object required
How/where to define userform object?
Instead of having the goals and objectives already in the form I would like to have buttons that would allow my clinicians to add as many goals and objectives as they'd like to the attached treatment plan. I'd like them to be able to click a delete button as well when they review the plan and need to delete a goal that's been met. When my team clicks into the second worksheet they would see rows 10-16 (below). They would then type a STG 1 and an Obj A. They then would click the Add Obj button and add as many objectives that they needed. Then when they clicked the add goal button a blank copy of rows 10-16 (including the Add Obj button) would pasted one row down and would update to read STG 2. Neither of these buttons would appear when the document printed.
Here is a screen scrape of what I'd like the attached form to look like before goals and objectives are added.
add goal.jpg
The original form is saved in an .xltm format but I saved it in an .xlsm format so I could upload it here.
HCT ITP (2-25-14) Abbr.xlsm
I found a thread on this forum that came close to answering my question but my attempts to pull out the coding that I needed was unsuccessful. Here's a link to the thread: [URL] ........
I have never written or used a macro before and I have a simple macro task to complete:
I have Column L (L4:L10) of seven scroll bars that move according to number changes in column F (F4:F10). I want to create a form control command button that resets the changes on the scroll bars according to static column of numbers in Column E (E4:E10).
I don't know if it matters, but I'll add that I plan to add a second button that does the same thing with a different column of data. I assume I'll simply replicate whatever procedure I use in the first button - is this okay?
I need to extract proper unit price of a component from a large data base. So far, I have created drop down lists so that the users can select different parameters for each component. How do I use the user selected parameter to pin point the proper unit price from the large database?
View 3 Replies View RelatedI want to create sections in excel exactely like below. I need to show user how many types systems are available and in each type how many config systems available.
View 4 Replies View RelatedI had a Calendar Control (2007) but now stuck using 2013. I have to change all of codes related to my Calendar Control to a Month View Control. Problem is I had 4 command buttons beside my calendar to automatically add value to the calendar:
My Code is in a userform. Standard code for MonthView:
[Code] ....
Basically it was “highlighting” the todays date but if one of the cmd_button was clicked then it would highlight the today’s date plus 15 (making the highlighted date, if we take today as an example: 2014/05/22).
Now with the Month View is there a way to select the exact same thing? I mean I know if I deactivate the “Show Today” in the properties but is there a way to make the selected date to have the “Circle in Red” around the date IF I click on a command button?
Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
View 14 Replies View RelatedIve created a form, from which I want to search and replace information into several sheets. Ex: When I select the button find record after entering a name, I want it to search through consecutive sheets to find the name and populate all information associated with that name into the form. From there, I would like to be able to edit that particular records information in my form and then select another button on my form update record to have the information automatically replaced back into wherever it finds that records name in the sheets.
View 9 Replies View RelatedI have got a table, with a range B1:CC200. I have been able to run a macro which pops up a form and shows the particulars of a selected month corresponding to those in the table. (e.g., say entries in column D pertaining to say, March 2012 are shown in the form). Problem is I want to be able to update the entries using the form, but no no success. Am quite new to vba but the code I tried to use on the command button after updating is shown below
Private Sub CommandButton1_Click()
TextBox1.Value = Application.Lookup(Range("A1"), Range("B1:CC200"), Range("B2:CC2"))
TextBox2.Value = Application.Lookup(Range("A1"), Range("B1:CC200"), Range("B3:CC3"))
.
.
.
TextBox20.Value = Application.Lookup(Range("A1"), Range("B1:CC200"), Range("B20:CC20"))
Unload Me
End Sub
Where A1 is the month in question, B1:CC200 the range of the database and row B2:CC2, and B3:CC3...., B20:CC20the data I need to update. .
I'm trying to pull up a second form from a command button within a form. There's a command button in a sheet to open the first form (frmOrderInput.) Then there's another command button in that form to open the second form (frmPriceInput.)
The Module to open the first is this:
This is probably really straight forward but cant see why it happens, the following macro works fine when called by a button created by the form toolbar but doesnt when called by a command button, get the runtime error 1004, "select method of range class failed"
ActiveSheet.Select
Range("B4:B37").Select
Selection.ClearContents
Range("G4:G37").Select
Selection.ClearContents
Range("B1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("F1").Select
Selection.ClearContents
Range("J1").Select
Selection.ClearContents
Range("M2:M3").Select
Selection.ClearContents
Range("B4").Select
ActiveWorkbook.Save
Application.Quit
I am imagining a spreadsheet with the equivalent of an info button in each cell of column A. The idea is the user clicks the button to open a User Form containing additional info about the entitity represented by values on that row.
E.g., Row 1 is labels, row 2 is all about Bob, 3 is about Jane, 4 is about Freddy. If the user clicks the info button on row 3 then a user form opens up and displays additonal info about Jane.
I am wondering if there is a way for VB to identify which row the clicked button was on without having to write individual code for each button telling VB which row the button was on.
I am hoping there is some form button property I can reference or interrogate which will give me a cell address; a button.position property or similar. This will obviously save me a lot of coding if VB can dynamically determine which row is being interrogated as there will be a couple of hundred of these to start with, potentially thousands. (I realise Access is a better solution for database interrogation, but I have to work with excel at present).
Ultimately, the user form that the button calls up will have a list box on it and I want the list box to default to the entity on that row, and allow the user to browse the other entities if they want to.
If there isn't a solution for this I will just have a single info button for the users to click that opens the user form and then they can select the entity from a list box. But I would like to offer the "intelligent display" option but don't relish coding the equivalent of several hundred "You clicked button four hundred and seventy six, default to info card for bert".
i have a form control listbox (list box 5), it is multi select, i need to create a for next statement that loops through the list in and tells me which "row numbers" as it were are selected. i.e if the 1st and 3rd ones are blue, it returns 1,3 in a cell? (lets say cell A1).
View 2 Replies View RelatedI have a control sheet that I am building and my sliderbar is making my data just disapear and not replacing it with any new data. Can this be fixed if I am able to reverse the sliderbar
View 2 Replies View RelatedI am trying to use the Chartspace object on a VBA form in Excel 2002, but am unable to find out how to specify the speadsheet data to be used for each series. I have found out how to add series, and to add titles & legend etc.
View 5 Replies View RelatedI have many Form Control Check Boxes that all link to another sheet on row 3.
I have many changes to make but only want to implement the change related to the check box.
This code works perfect when you manualy type true or false on row 3 but not if the check box makes the change.
Private Sub Worksheet_Change(ByVal Target As Range)
ThisCol = Target.Column
If Target.Row = 3 Then
RESULT = MsgBox(Cells(1, ThisCol) & " = " & Cells(3, ThisCol), vbOKOnly, "CLICK RESULTS")
End If
End Sub
Why does this not work when a check box changes the value in row 3?
Does anyone know how to make a form control (ex combobox, textbox) have multiple columns so that it would behave like 4 controls in one. What im going for is a control that looks like the control used when setting windows system time "12:30:00 AM" So "12" is in col 1, ":" is in col 2, "30" is in col 3, ":" is in col 4, "00" is in col 5, and "AM" is in col 6.
View 7 Replies View Relatedhow to link VBA to the buttons on user forms:
[url]
Now I'm interested in some refinements to make this little toy I'm building work faster. I want the user to be able to enter data without having to click the text box in order to proceed. My code
Userform1. show
just brings up the form with a text box in it, and the user has to click in the box to get started. Is there code I can add that will put the cursor in the text box so it's ready to go?
I suppose this seems trivial, but it will speed the data entry part of this little project up and every little bit counts.
.
.
.
I am looking to connect 2 form control boxes and have the second box run 2 different types of macros. The first box will have only 2 options - select by week and select by month.
The second box should show the list of weeks or months based on the selection in the first box. Then for the second box, if weeks is shown, a week macro should be run whenever a week date is selected. Similarly for the list of months, a month macro should be run whenever a month is selected.
I have been trying to do this for more than a week (after posting on this board) without success. I apologize if this seems like a duplicate post.
how to get a list of the properties for form control shapes (not control toolbox shapes) that are placed on a worksheet (not on a userform). Eg., a button, checkbox, combobox, etc.
If it's possible, I'm interested in working with properties like "enabled", "caption", etc. that aren't listed on the "Format Control Properties" dialog.
I understand you can edit properties of a form control shape via VBA code (See example below), however, I can't seem to find anything within the object browser about them.
EXAMPLE
With ActiveSheet.Shapes("Scroll Bar 2").ControlFormat
.Min = 10
.Max = 150
End With
I am working on an Inventory control worksheet where i have all parts used in a certain assembly on the left (A) followed by Qty per assembly (B) and then current baseline Inventory (C). In columns D-max i will have at the top a pull down menu to let the user decide if they are subtracting from inventory (Purchase Order) or adding to inventory (Fulfillment). Under both circumstances, depending on which is selected I would like a different form to pop up which allows the user to enter values to control the chart.
If Purchase order is selected then a pop up will ask the order number, date, and quantity. This will then fill in three specific cells in the chart which control an equation to subtract the number of parts based on the order quantity.
If Fulfillment is selected I would like a pop up or the entire parts list to appear with a field to enter the number of parts being stocked. The program will then add these parts to the previous inventory entry.
In the example attached you can see that right now you have to manually enter the number of units ordered, then an If statement takes over to calculate the new inventory level (If statement used to keep things neat and possibly to incorporate fulfillment at a later time). At this time there is no way for me to enter stocking transactions.
how to insert an attractive form control button instead of the horrible default grey buttons that come with Excel? I've seen it done, so I know it can be done.
View 3 Replies View Related
