When I put form controls onto a spread sheet is there anywhere I can easily see and access its properties? Format Control is lacking many values and I do not see them in the vba project toolbar. I am trying to get checkbox locations and checkbox numbers if possible. If there isn't an easy place to see these is it still possible to get the checkbox locations in VBA?
View 7 RepliesI'm trying to tweak this code from a previous form I created but I'm getting a compile error message. On my old form...the first box was a combo list box where the person entering data would select a value. On the new form, its a text box where the person will enter the value. I thought I could just change the me.cbo[name].listindex to me.tbo[name].listindex - but that seems to be causing the issue. I'm not sure what I would put after the me.tbo[name]. to get the code to run...
Code:
Private Sub cmdAdd_Click()Dim lRow As LongDim lPart As LongDim ws As WorksheetSet ws = Worksheets("DataNEW")'find first empty row in databaselRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).RowlPart = Me.tboProdCode.ListIndex
I'm having trouble trying to come up with a way to insert data fields into a spreadsheet form. I have a travel authorization form that I would like to have automatically fill in the required fields based on typing in a name. i.e. I would type in an employees name, and it would automatically fill-in the correct address, etc for that employee. I have attached a spreadsheet that contains one sheet as the form, and another sheet containing the employee data. I know nothing about VBA, but I have a feeling that is where I need to go.
View 3 Replies View RelatedI have two linked ComboBoxes on a form. There are 10 Textboxes populated by ComboBox2’s choice. These controls are situated on two frame controls which are used for visual groupings only. I would like to clear everything except Combo1 when Combo1’s value is changed. ( Combo1 is a filtered key list using the dictionary. Script code)
I’ve written a small sub that is called to clear the textboxes, but its not reliable every time. It seems that if the scroll bar is used, and the user chooses Combo1 choice “NUTS/SEEDS”, the boxes aren’t cleared. In fact it will show the “Walnuts” info in Combo2.
Is there a short way to clear text boxes, check boxes and option buttons in one command. I dont want to delete them I want them to be able to accept more data once the operator has finished
I have form controls on my worksheets. How do I hide them? When I hide the row, the controls stay there. These controls don't seem to have that visible property like the VB control.
I'm thinking of using a validation->list instead, but then you don't see the drop down arrow.
I have 30 textboxes on my form, named "TextBox1" to "TextBox30".
When the user clicks the button to launch the form, I want to first hide all the textboxes (already done).
Then I want to loop from 1 to some predetermined number (between 1 and 30), and update the textbox whose name contains the number of the loop. For e.g.:
Sub ShowForm
Dim ctl
For Each ctl In frmKFF.Controls
If Left(ctl.Name,7) = "TextBox" Then
ctl.Visible = False
End If
Next ctl
I cant get this to work, it comes back with run-time error '424': object required
I am using form controls option button not activeX controls.
HTML Code:Â
Sub OptionButton12_Click()
If OptionButton12.Value = True Then Range("D14").Value = "Alamo"
End Sub
Sub OptionButton13_Click()
If OptionButton13.Value = True Then Range("D14").Value = "ERAC"
End Sub
Sub OptionButton14_Click()
If OptionButton14.Value = True Then Range("D14").Value = "National"
End Sub
Imagine I have a form with a few controls on it. And the controls properties are set up at runtime.
Is there any way possible to save the property changes that were made at runtime to the controls themselves? I mean - short of manually editing all the controls at Design time?
This might be easier to explain by example. See dummy code below. Using this example - I want to find something that will save the Caption of CommandButton1 as "TestMe" (rather than have it only temporarily set at runtime).
My form controls (cmd buttons) and images don't appear or don't work. I was working on a macro to cut and paste a set of cells (protected sheet, I did unprotect it), and I noticed the cmd button tied to this disappeared. It then re-appeared but I couldn't engage it. This is part of a larger workbook that consists of a single page of cmd buttons that release specific worksheets (no activex). These buttons disappeared and re-appeared and don't work either. When I select the button I can see the cell underneath light up. It's like the image is there but nothing else?
The macros run fine from VBA editor. I was thinking the issue had something to do protections? There are more than 5 passwords (set on a worksheet) being used here, there is a fair amount of protecting and unprotecting going on, and I loop through the worksheets several times. Everything was working fine and now I'm stuck. I was getting a the-object-invoked-has-disconnected-from-its-client error related to an "insert cells" command on a co-workers computer, but not on my machine. This is actually what I was trying to solve when it crapped out. I would delete the buttons and create them all again, but I can't even select them to delete them.
I am creating a form that performs several different functions. I would like to force a sequence of these functions by enabling/disabling the controls based on a variable.
For example, a form as two buttons Button1 and Button2. By default, Button1 is ENABLED and Button2 is DISABLED. When Button1 is clicked, a macro is run, where an Enable_Button2variable is initialized to FALSE. The last line of the macro sets the Enable_Button2 variable to TRUE. Then, because Enable_Button2 is now TRUE, Button2 then becomes ENABLED.
I am trying to apply this strategy to a ComboBox, ListBox and CommandButtons. I'm not sure what event to trigger on to poll the Enable_Button2 to enable/disable the control.
I have been working on large project using Excel VBA for several days. My code seems to be working correctly, but I have more to do and now, when I drag an object from the toolbox onto a UserForm, the object is not added to the list of objects on the form. If I go back to versions of the project that I was working on several days ago, there is no problem. If I run "Workbook Rebuilder", the objects that I have dragged onto the form are then added to the object list, but I still can't add new objects to forms from within the VBA editor. Is the project corrupted, or is there some other explanation, and are there any fixes? The code runs about 50 pages, and there are over 20 forms, so redoing from scratch is only a last resort option.
View 3 Replies View RelatedI am trying to dynamically add controls to my user form based on some values in my cell. I am successfully able to create a text box dynamically but my label is not getting displayed. here is my code
Private Sub UserForm_Activate()
On Error Resume Next
If (ThisWorkbook. Sheets("Sheet2").Cells(1, 8) <> "FALSE") Then
Dim ctl As Control
Dim ctl1 As Control
Set ctl1 = Me.Controls.Add("Forms.Label.1", ctl1, True)
With ctl1
I need to make several exact duplicates of an existing worksheet which includes several form controls. Basic Copy/ Paste the worksheet works well but row heights are different and hidden rows are unhidden. Is there a way to make an EXACT duplicate ?
View 2 Replies View RelatedI have a worksheet with lots of things going on... the end user needs access to the Name field, DOB field, all the scroll and form control option buttons in the 9-question sheet, and the macro-enabled reset button at the bottom. Formulas are sprinkled throughout the worksheet, in columns T through AC... option button links go to AA and AB.
I would like to protect the entire sheet so none of the fields, except where indicated above, could be selected or edited... but when I protect the sheet, I get errors when trying to use you form controls, and the formulas do not respond. And finally, when protected, I get a debug error on the reset macro.
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.
How do I save an excel sheet as a fillable form with only certain cells allowable to be filled and than after the form is filled and the automatic calculations(formulas) are done can I save each form seperately.
This is like a student report card.
You will enter students name and his grades and the form will do calculations than i want to save each completed form as its own file
I am trying to do is create and link and user form to put information into a spreadsheet. Layout of the form is done but I'm not sure how to link the cells. Also, there are some cells pre-filled that should show up on the form but I don't want the user to change those (columns a,b,c - guessing i will protect those).
Would be great if the user could navigate rows within the form so I included buttons for previous and next entries (this feature would be very similar to the built-in data form that excel provides). My sample spreadsheet is attached.
I have two groups of option buttons. One group has 15 option buttons the other has 2 option buttons. When I click on one group it deselects the option button in the other group causing no buttons to be selected in the second group.
In other word only one of the 17 buttons is shown selected at a time.
By "selected" I mean the filled in circle is shown within the larger circle.
The two groups of buttons are linked to different cells in the spreadsheet.
How do I keep the two groups from interaction with each other?
I’m looking to use a user form to populate a quotation template, I have been able to link the first part of the form to the sheet as these are static cells but I’m struggling in getting the items details area of the form to work as I need. Below is a brief description what I need.
Item Details area
Move down a line after every time the add button is pressed and clear contents of the text boxes for the next item.
Also I want to make each * denoted item required before you can add a new item
I’ve uploaded a copy of the file if my description isn’t too clear
I have a userform with a spreadsheet in it and I am inserting data into a workbook(my data base) with this application. In the spreadsheet changing event I used "0.00" number format (this is what I need) but the results after I type a number is diferent from what I just typed. i.e: if I type 13.56 there is no problem but if i type 6.56 the number is transformed to 20607.00. I have tryed a lot of methods to stop doing that but all of them failed. how to use decimal number format without this problem
View 6 Replies View RelatedI am a personal loans officer and have created a worksheet to input a range of information about an application. The information is personal details and details about the loan. The data I input runs down Column D [with line breaks to seperate the answers into categories] with the question beside it in Column C.
I do this for every new application as I use it for reporting on my loans as well as mail merge to produce documents for the loan.
Currently I am doing one per loan but would like to have a button at the bottom of the page that once clicked, it will push the data onto another worksheet so that all my loans for that month are on the one spreadsheet. The data will be pushed onto a row, not a column, but it will also need to find the next available row to put the data on.
Once the button is clicked, the first worksheet will clear ready for the next loan to input and the data will transfer to the 2nd spreadsheet. And, if possible, on the first sheet will be a drop down list of all the current loans so I can recall any application details to the first sheet if needed to update details. So if the 'loan number inputed' already exists, it wont create a new line on the 2nd spreadsheet, but will know just to update the fields.
I have a userform that will be a 'work order' for repairs. The ladies in the office can fill out all the blanks that will be entered into the spreadsheet. Each time someone is done, it will plug the information on the next available row on the spreadsheet. Easy enough.
What I need is putting a 'work order number' on the form. I have put numbers (101, 102, 103...) in Column A on the spreadsheet. I would like the form to pull the next available number and put it at the top of the Userform. I want this number to be 'locked' in so the ladies can't change it.
My goal - when the ladies are filling out the form, they can see what 'work order number' has been assigned and can give this number to the maintenance.
I have 4 textboxes that the user enters data. I then have a button on the user form.
i would like to write code so that when the user hits the button...
Textbox1 data goes into active cell
Textbox2 data into the cell below Active cell
Textbox3 below it
and textbox4 below it
then close the user form.
I have a large spreadsheet (1000-plus entries) that all have about 10 or 15 columns of information (product title, product number, etc).
I also made a form that I want to use as a master form, where all the titles will not change.
What I am wanting to do is, by going off the product number, when you click it, this pre-made master form will pop up, and all the information that was located on the large spreadsheet for this product number will be inserted where it is designated to be on the master form.
While I could probably do something like this through Access, we need to keep it on Excel, and in the same workbook so that it will automatically update things once I put a new number or input onto the large spreadsheet.
I have created a userform but just unsure how to format the code in order to get information to appear in a spreadsheet
http://www.srfl.ca/userform.htm
I bought a Surface 2 tablet. When I go to "settings" in the Trust Center it does not show any option to enable Active X.
View 3 Replies View RelatedI often use the .TAG property on controls as a sort of temporary container to hold variables, arrays and such but this gets messy because I sometimes forget which control I used for what since there appears to be only 1 tag property per control. Are there any controls that have numerous .TAG like properties I could use (even name) to hold my temp data?
Excel 2007 and 2010
Additional Controls Dialog Box refuses to display
I have a problem trying to add additional controls to the Controls toolbox
The two methods I've tried
1. Right click on the controls toolbox , click Additional controls
2. Menu, Tools, Additional controls
Neither of the above produce a dialog box containing the list of additional controls to choose from.
I am running Excel 2007
In VBA (Excel 2003), I'm wanting to get a reference to an ActiveX comboBox that is inside/attached to a cell.
I can iterate thru all the OLEObjects in the OLEObjects collection of the Worksheet and test the .TopLeftCell property to see if it matches my target cell. However, this seems like a long way around the problem - particularly if there's hundreds of comboboxes in the sheet.
Intuitively, I would have thought there would be a Cell.OLEObjects collection, but it appears that there isn't.
Is there a quicker and easier way to get a reference to the OLEObjects within a target cell?