I have a spreadsheet that records data on various sites with data entered on a userform.
Every so often a site may need to be added or deleted. Is there anyway to use VBA to add an additional field to a Userform, and then also delete the field if necessary?
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 am using a spreadsheet as a score sheet for a competition. One of the columns is the student's GPA. After entering all the scores there are duplicate final scores. I need a way to have it look at the final score and then use the GPA so that it will not put a duplicate value in the final column.
al Column N is the Total Column, Column O has the Names that correspond to the Total Column. Currently I am taking this total and putting it into Column Q (High Scores) in high to low order. Column R should have the names that match the scores. But with duplicate scores, it is only putting the first name associated with the score. I would like to use the GPA as a final determining factor for the duplicate scores. The higher GPA would come before a lower GPA. I have tried to put an additional column to bring the GPA over to correspond with the High Scores Column, but could not get it to work.There are actually more names for the competition and the top 10 will be moved to a different sheet and further judged. I have attached a sample with the exact formulas that I am using.
I have some code which adds a series of textboxes at runtime depnding on some info on my spreadsheet.
Due to these textboxes being added programmatcally, they curently have no macro's assigned to them.
I was wondering if there was a way of adding code to the textboxes at runtime.
Dim choosebefore As MSForms.Control
Set choosebefore = Me.Frame1.Controls.Add("Forms.Textbox.1")
With choosebefore
.Name = "Before" & i
.Left = 0
.Top = (((i * 18) - 18) - 8) + 6
.Width = 24
.Height = 15.75
.Visible = True
.TextAlign = fmTextAlignRight
.Text = Chr(149)
.SpecialEffect = fmSpecialEffectFlat
.BorderStyle = fmBorderNone
End With
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).
I am using this code to open a form in Excel:
Code:
Private Sub Workbook_Open()
Form1.Show
End Sub
It has worked perfectly for about 2 weeks, well now when I go to open the workbook it gives me the debug error of Run Time error 424 Object Required. I last ran this today at about 8 a.m. no errors, but now about 4 hours later, it is bugging out. What is causing this?
I have a user form that builds a workbook from one template sheet and information in a SQL Server database. The template sheet contains 4 charts that are copied to every new sheet in the workbook, then their data ranges are set through VBA code. For a while the code was working wonderfully. All the necessary sheets were being generated and correctly populated.
Yesterday I added a new module to generate another sheet and pull data from the same SQL Server tables. Now if I run the userform it will work fine once and create all the needed sheets, and set all the charts to the proper ranges. However, if I delete all the created sheets and run the form again, I get:
Runtime Error '-2147417848 (80010108)'
Automation Error
The Object Invoked has disconnected from its clients
The error consistantly occurs for the following line of Selection.Copy Destination:=Worksheets("Master").Range("D1")
After the error the selected range still shows up as surrounded by a selection box, but if I change sheets, the box moves to the new sheet. Also I cannot select any new cells in the work book. In order to get Excel to work again I have to End Task from the Task Manager just to get it to close. When I restart Excel and go back to my work book I get the same results, i.e. The form works great once, then Excel dies.
I have tried qualifing all of the objects that can be qualified. I have tried setting all possible varriables to Nothing before exiting my macros. Nothing I have tried, or found suggested so far in the forum works.
I have a form set up with excel (2003) using vba. I'm doing a check to see if textbox1 is empty, if it is, a msgbox pops up with a warning that "textbox1 needs to be filled out". I click OK and the cursor goes to textbox2. I want the cursor to return to texbox1 without user intervention but can't figure out how. I'm using "Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)".
I know it can be done because I did it with vba Access, but I can't seem to find it using vba Excel.
creating this excel file with multiple comboboxes, i was wondering if i can add another into the equation. so i can have sub-categories.
i have attached the original file and how i would like to change the layout!
i also have another question, see with the info that would go into the textbox is there anyway you can put writing on seperate lines,
eg:
Lettuce
Mayo
Tomato
all within the same cell?
I need to use two types of fonts to not only display a string of characters in a textbox but also past to a cell as viewed in the textbox.
An example string:
[#|:.010(M)|A|N(M)|B(M)]
The above string in Arial font would look like this:
[TP|Ř.010(M)|A|N(M)|B(M)]
I am simulating a GD&T instruction (Geometric Deimensioning and Tolerancing).
My dilema stems from the first character "[" and the last character "]".
I use both of these characters to open an instruction and close the instruction (as if all characters are surrounded by a rectangle).
What I cant figure out is how to isolate the first "[" and the last "]" (They will always be the first and last characters of an unknown string length). The first "[" would be an "Arial font" and the last would be an "Arial font" everything in between is another font (in this case Verisurf).
See attached example jpg file.
"verisurf1.jpg"
Verisurf uses both "[" and "]" as alternate characters. I only need to capture the first and last character if: 1. The first is a "[" and or the last is a "]". They will never be vice~versa.
I have a UserForm that utilizes the Change event of a Textbox.
The form goes through some validating before any calculations happen.
My problem is if the user wishes to backspace, clear or perform any changes to this textbox, it bugs out.
Here's the
File attached
1) I have a user form that picks data from a spreadsheet - This is for scheduling a course. Works fine
2) I have a second form which should do 2 things:
a) On the bottom half of the form, select people from sheet 'Staff'. Works fine
b) On the top part, I would like to select a course (using combo box) and fromt this select a date for when the course has been scheduled (from sheet 'Scheduled Courses'). Courses are run a few times so have multiple entries. With some help, have done this and it works well
The problem is that when I select a date for a course using combobox2, I would like to populate the text box 6 & 7 with the cost of the course and the location. This goes wrong as it starts taking cost and location from the first cell. Eg when i select SPIN Selling, all is fine as this are the first courses. However If I take 7 Habits, it gives me costs for SPIN. If I take OM, it still gives me costs for SPIN. The dates in combo box 2 appear fine
What I've done so far is... Created two buttons (Add and Find) in my Excel sheet1. If I click my Add button Form1 opens. In Form1 the user enters a number of values that are inserted into sheet2 of my Excel book. If I click my Find button Form2 is displayed. This form contains a multicolumn textbox that shows some of the columns from sheet2 that have values in them. Now to my BIG problem....
I would like to be able to select a row in my multicolumn textbox, click my OK button there and then Form1 should be displayed with ALL the values from that particular row from sheet2. As I mentioned I'm very new at this and I'm not even sure how to do this logically and much less the correct code for it. I hope that someone could thoroughly explain to me what I have to do and why to be able to get this working.
Private Sub UserForm1_Initialize()
Dim rIds As Range
Dim MaxId As Long
Set rIds = Worksheets("Systemtest").Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
MaxId = Application.WorksheetFunction.Max(rIds)
With Me
.IdBox.Value = MaxId
Private Sub DateBox_Change()
DateBox = Format( Date, "yy/mm/dd")
End Sub.....................................
How can I allow users to either select the SKU from the dropdown menu, or to type in the SKU # in the dropdown menu (C5 through F5) and have it populate the data fields for that SKU when they press the 'Enter' key?
Currently using Data Validation ='KVM Comparison Data'!$B$4:$OP$4 and cell logic to allow the information to be pulled from a hidden tab =INDEX('KVM Comparison Data'!$D$2:$OP$65,MATCH('KVM Product Comparison'!$B6,'KVM Comparison Data'!$A$2:$A$65,0),MATCH('KVM Product Comparison'!C$5,'KVM Comparison Data'!$D$4:$OP$4,0))
In short, I want to keep it the way I have it, but allow users to also have the option of typing in a SKU and pull the data up that way. I know this is possible, I just can't figure out how to do it, while keeping my current solution in place as well.
why the text box "txtjobnum" wont populate with the active cell in my "COMPLAINTS" sheet. The green code below is in the userform and the red text below that is in module 3.
Private Sub cancel_Click()
Unload Me
End Sub
[Code]....
I have this code inside a userbox to auto populate a textbox on the same form, the combibox info is located on row E, and it populates the textbox with info off row G, but how can I change this to pull the info off row C instead of G???
Code:
Private Sub ComboBox1_Change()
var1 = WorksheetFunction.VLookup(ComboBox1.Value, Worksheets("Basic").Range("E11:G90"), 3, False)
TextBox1 = var1
End Sub
i have workbook that has form to login
what i need is how to get the data from textbox every time user login(fill the textbox) ,and put it in specific sheet with range.offset ?
I have written the following snippet in my code, the purpose of which is to cycle through the text boxes on a form (frmPaymentOrders) and transfer the value of each text box txtPO1 to 10 to the variable PORow (Dim as Long). These will then be pasted into the correct part of my worksheet.
But I keep getting an "Invalid Qualifier" error - even though I think I have correctly qualified the PORow variable as Long.
Code:
For i = 1 To 10
PORow.Value = ["frmPaymentOrders.txtPO" & i]
Next i
After a bunch of false starts I am not getting very far and after searching the message board have failed to come up with the right clue.
I am trying to create some vba code that would use input from a form to create a file name that would save the file in the current folder.
e.g. Foghorn Leghorn expenses Aug 10 2008.xls
I am using calendar control named “calendarFinal” and a text box named “txtName” . The text box to show the user the suggested file name is txtFileName.
Among other things, the date from the calendar control gives a date like 08/08/2008 and I suspect that the slashes are going to give me grief.
A confirmation message box would be nice. (vbOk?)
I have a form that pulls up. When the person enters in a ticker symbol I would like the VBA code to perform a lookup in a range "Data_Company_Names" (3000 lines long of company ticker sybols and names) and use the corosponding Name to fill in the textbox for the name. If no Ticker symbol is found then the textbox would be blank.
I realize I would have to use some type of Private Sub TextBox1_Change() code but I am not sure how to have it lookup in a range and return the corosponding name to the ticker. For example, when the form pulls up and the person enters AAPL in Textbox1 then Textbox2 value should equal the Apple which is the company name located in the column over from AAPL in range Data_Company_Names
I want one procedure that will validate the CURRENT textbox (not named by name, to allow for the procedure to be included in the change event of several different textboxes), to see if it is numeric (decimal places allowed) in Excel 2007.
I followed the instructions here: {url}, which describe exactly what I want to do. So I put the final procedure listed on that page (the dynamic validation code) into the private module of the user form object as listed below:
Private Sub pipes_Change()
OnlyNumbers
End Sub
Private Sub OnlyNumbers()
'This procedure checks to see if the value
'of the current textbox is a number or not
If TypeName(Me.ActiveControl) = "TextBox" Then
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers are allowed."
.Value = vbNullString
End If
End With
End If
End Sub
I then ran the form and typed a number into the "pipes" textbox that had been validated. A compile error is thrown: "Invalid or unqualified reference", highlighting the ".Value" portion of IsNumeric. I tried to remove certain parts of the code, such as the IF statement and the "And .Value <> vbnullstring", but nothing works. I have a feeling it is because this code was created for Excel 2003 (though I'm not certain).
Error Help.xls. An example is attached. If you open the attachment, you can see that I'd like to choose a date from the combobox and have the corresponding "date code" (that's what I'm calling it - it's for a different, more complex purpose) appear in the text box. For this to happen, I'm using the following
View 2 Replies View RelatedI have a form that adds and edits data on a sheet. I would like the user to be able to select any row of that data and then a macro copies the data from column A for example (ID1234). Launch my form (FmAddTask) and then paste it into the combocbox (idlookup).
Hence enabling immediate editing of that row of data via the form.
I made a start:
I have a question regarding frames and textboxes on a user form.
I have a user form that has a number of textboxes populated from the results two Comboboxes.
I decided to group some of the textboxes in frames for more logical viewing.
I grouped the textboxes, moved them, placed two frame controls on the form then, finished by replacing the grouped textboxes. No code was altered or added.
When I ran the form, only the Comboboxes were functional and the text boxes were blank.
There were no runtime errors or compile problems.
I am writing a macro to automate the filling up some data from excel to company website. I have changed the website name in this post for data protection purpose. The excel will login for different clients by using the combination of username and password for respective clients and then some data are required to be inserted in a text box on a web page, I think the text box is on a form and form is within an iframe, within the web page. Once the data is inserted into text box, one button (Submit), which is also on the same form, is to be clicked.
On the click of a button, the updated data appears on another section, I could not make out if it is an form or frame, which is under the abovementioned form. Once we are happy with the way data appears on the web page, we have to click another button (Update), which is on the same section, to finally updating the data on website.
I wrote the following code to login to the website and then to navigate to the web page where I have to fill up the performance numbers in a text box.
The first problem is how to access the text box inside the form from VBA so that the macro can write a number in that text box and how to access the button to submit the data. The HTML code, which can be seen on click of F12, is attached below.
The second problem is how to access the Update button inside the other section, so that the data will be finally uploaded.
VB:
Sub LoginToCorpAccount()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
[Code]......
I have a userform with numerous text boxes. One textbox is used to input a date with the following code
[Code] .....
When I have completed my userform I want to export this date as a date into range(a2) so I can then use the filter function to filter by month or year. THe problem I am having at the mo is that in the userform it is formatted as dd mm yy, but when I export it from userform it changes to mm,dd,yy. So if I entered 12th January 2014 it changes it to 1st December 2014.
A workaround is to export as text but then I use the filter by month year function so would prefer to avoid this.
I am attempting to format some TextBoxes from within a For/Next loop. I need a way to check which TextBox is the active TextBox in the loop. Using i as the variable, I came up with this code snippet: Me.Controls("TB" & i).Text = Format("TB" & i, "mm/dd/yy")
If i = 3, this gives me in TextBox3 (which is called TB3) the text 'TB3' and not the value of what is in TB3. It has got to bo something simple, I just can't see it!!!
I am working on a project that requires Other or Additional info to be available if I click on a specific item. For example, if I click on a person's name a message or pop up show appear with the person's address or any other information I might want to add. Is this possible in Excel or should I be using a different program?
View 1 Replies View RelatedI have a macro attached to a command button to clear entries in my workbook for me however, I left out one tab that needs to be cleared.
I need to add the following tab and cells.
Add Tab "Time Off Glance", Cells (B1:P35) to the same command button.
The code I have now is:
Private Sub CommandButton2_Click()
Dim rng As Range
Dim x As Integer
Dim y As String
For x = 1 To 27
y = x
Union(Sheets(y).Range("B9:O15"), Sheets(y).Range("Q9:T15")).ClearContents
Next x
End Sub
I am trying to find some better looking command buttons for userforms in excel VB.
Maybe like the ones you can use in Access from the command button wizard. inoculars for find, door for exit etc. Does anyone know how to get these in excel VB editor?