I have a text field at the bottom of a user form that remains hidden (i.e. visible = false) until the user clicks the "Ok" button. At that point, I want the text field to appear as the macro is running (it's a large macro, so the text field just says "processing, please wait...").
The first line of my macro is:
I have cobbled together (borrowing from examples I have found online because I don't know VBA) a form which inserts a date into a spreadsheet.
The trouble I have is that this date field is in USA date format and I need UK.
If I enter 03/01/2009 into the form, it appears in the spreadsheet as 01/03/2009. I have formatted these cells to display the month only and because of the way it was entered, the next column displays MAR instead of JAN.
Can I validate the input value in a form?
how to rotate a label to a 45 degree angle on a user form.
View 9 Replies View RelatedHow do I define the variable in a text box so that it stays in that text box after the close of the dialog box. For example, I enter "Test" in TextBox1 then close UserForm1. I want to be able to open UserForm1 back up and still have the same user defined text in there.
View 9 Replies View RelatedI recently created a userform. I want the data to be placed into the leftheader, centerheader, and rightheader of the document after the user fills in the required fields. I have made the userform, and I can make the code for it to place one item on my userform in the left, center, or right headers. However, when I try to place more than one item in any of the headers excel deletes the first item and overwrites it with the item that is listed last in the VBA code for that respective header. I am new to VBA (this is actually my first program) so I do not know how to make the first value stay there and simply have the next value placed on the line below it.
View 13 Replies View RelatedI have a TextBox and 2 Commandbuttons. I need the cursor to return to the TextBox when the Userform is loaded without the user selecting the TextBox.
View 3 Replies View RelatedI have a user form with 4 text boxes and a command button. I have no problem looping through the textboxes to retrieve the value, but what is the best way to detect if any duplicates exist between the four text boxes? For example, in the command button's
If textbox one's value is "test" and textbox four's value is also "test", it should throw an exception. If a question like this has already been answered, a link to that post would surely suffice.
I am trying to create a user form to edit some named ranges. The VBA user form designer is basically the same as Borland Delphi, so building the form itself was easy. What I can't figure out is how to populate the user form with the data from the spreadsheet.
I have a number of text boxes on the form with names like txtTier1Slots, txtTier1Ceiling, txtTier1Floor, etc up to 4.
I have a defined name that corresponds with each item, Tier1Slots, Tier1Ceiling, Tier1Floor, and so on. I'm using defined names because they're referenced in Conditional Formatting on one of the worksheets in my project. They don't exist as actual cells, just name references.
I want to get the form populated with those values. I've tried about twenty variations of the following code, using ThisWorkbook, Workbooks, Cells, Range, Name, and just about every cell reference method I can think of. I've so many different error messages, my head is spinning.
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 have a user form which includes some text boxes (txtRUL.text for example) where I want to enter a number as a percentage. I divide the number entered by 100 to avoid Excel automatically multiplying by 100 and the result is displayed in the user form as the correct percentage.
Private Sub txtRUL_AfterUpdate()
EnableSave
txtRUL.Value = Format(txtRUL.Value / 100, "0.0%")
(I'm sorry I cant figure out how to format this code as code in this post)
This works well, trouble is when I save and it writes the results to the worksheet, the numbers in the user form revert back to plain unformatted non percentage numbers (ie 5% goes to 0.05)
I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.
It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
I've been using the following code successfully for years. Today I would get the error 1004:Unable to set the visible property of the worksheet class. All my searches came up with someone not realizing they had protected the workbook. As you can see, the first thing I do before trying to set the worksheet visible is to unprotect the workbook. In frustration and on a lark I tried .Sheets("items").Visible = True and it worked. These kind of intermittent errors in Excel VBA are very frustrating. Can anyone tell me what I am doing wrong?
View 6 Replies View RelatedI have attached sample workbook that has a user form with 6 check boxes and 3 text boxes. The value of each of the text boxes is based on a cell value in Sheet2. I have the visible property of CheckBox5, CheckBox6 and TextBox3 all set to False. What I would like to be able to do each time the user form is opened is have the visible properties of those controls dynamically changed to True only if Sheet2 cell A3 has text entered in it.
View 2 Replies View RelatedFor some reason, I cannot get why I keep getting an error saying that excel cannot set the property of a pivotitem's visibility. I use excel 2003.
Code:
For Each pi In ActiveSheet.PivotTables("PivotTable1").PivotFields("Group Code Description").PivotItems
check = pi.Name
If check = group Then
If pi.Visible = False Then
pi.Visible = True
[Code] .........
I am having problems with some vba codes when I protect my workbook; 'I get an run-time error 1004 Unable to set visible property of the worksheet class'
The code I am trying is:
Sub Stats1_Return_TextBox1_Click()
ActiveSheet.Unprotect "meme"
Application.Goto Worksheets("BCM Database").Range("L15")
Sheets("Stats 1").Visible = False
ActiveSheet.Protect "meme"
End Sub
How to sort this. One more question is there away of protecting you vba code? stop users being able to view or edit them for instance?
I use Excel 2010 andexample.xlsx cannot make my code working because of the so famous errore above.
I have several pivot tables (pt) in different worksheets (ws), and a list of items stored in an array I created. I've written a macro for setting ON all items in pt except those ones in the array (listOffnet within the code). Everything works properly.
Now, I would like to do the complementary action: setting OFF all items that are not in the array. Unfortunately, I get the error at line:
[Code]....
find the attached workbook
I have a Database and user form, in the user form i have a field named “Vehicle No” this is a combo box from which a user needs to select the Vehicle numbers, and all these are working fine now, I need your help in the following:
When user selects the second field named "Select Vendor name" i need a pop up window which shows all the Vehicles belongs to the vendor which they have selected, and with the popup window user selects the vehicle number then the Vehicle number combo box should be filled.
Currently users have to select by scrolling through Combo box which takes long time and difficult to find by scrolling.
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
I have created a registration workbook for this year's youth sports league. All of the information is entered into a User Form and separated onto it's appropriate sheet designated by the child's age. Next year, I would like to use this year's workbook to look up returning players.
Will it be possible to add a "lookup" button into my form, or create a lookup program, that once the registrar clicks on the correct player, the information is plugged into the User Form, the registrar adjusts the age and any necessary info, presses enter, and the information is copied into the appropriate category in the new workbook? I haven't worked with User Forms long enough to know if they can be filled in that way, but if this can be done, you are the people who would know.
I'm using a series of user forms for data entry to a workbook, some of them open next stage user form on completion (OK cmd button).
All that is working fine, but I'd like the initial form to close on showing the next one. I've tried adding Unload and Hide commands following the frmInsertEntry.Show (Next stage form), to no avail, but I'd like this user form to close or hide at the end of the sub.
Private Sub cmdContinueType_Click()
ActiveWorkbook.Sheets("Records").Activate 'Select starting cell in record sheet
Range("N3").Select
Do
If IsEmpty(ActiveCell) = False Then ' Search for next empty cell
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
If optDrillType = True Then
frmDrillEntry.Show
Else
frmInsertEntry.Show
End If
End Sub
Need to correct code to resize all visible rows on a sheet based only on the text in the visible columns. I have tried the below code but when it resizes it is using the largest amount of text in the rows including that in the hidden columns.
View 3 Replies View RelatedImagine 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 would like to determine if the VisibleDrowndown is TRUE in the attached table.
The current macro sets it to FALSE in field 1. However, I want to identify when it is TRUE so I can run this macro.
Attached File : V1.xlsm‎
I am collapsing a field in my pivot table and it is hiding all detail behind it. The column that precedes it is a description of the account number. How do I keep the description visible when collapsing a account number.
View 1 Replies View RelatedIn my IE site there is a field which is a dropdown field with the value 'Not Known' and 'Specified Date'. If I select 'Specified Date' another field named 'Date' becomes visible where I can give a date. I was able to ensure that the value 'Specified Date' is selected using the following statement:
IE.document.GetElementById("startDateBasis").Value = "SpecifiedDate"I also was able to populate the date field with the statement:
IE.document.GetElementById("startDate").Value = SOA
Where SOA is populated with the date string I need to be given as input in IE.
My issue though is that, eventhough the data is provided by the macro the field 'Date' is not visible. So I have no way to know if the input was succesful until I submit the screen. I think I read somewhere that this is because of the way datebasis is populated.
How to get the field 'Date' made visible if datebasis is given as 'Specified Date'?
I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.
View 5 Replies View RelatedI'm putting together a spreadsheet to do with football betting, that involves calculating various stakes and odds and probabilities. I've done pretty well, but I want to take it to the next level.
At the moment, I have 4 sheets which are completely self contained:
sheet one = betting on 2 matches
sheet two = betting on 3 matches
etc
This was the easiest way to do things when I was learning.
Now I want to look at incorporating more flexibility into my spreadsheet. I want to investigate the possibilities of having a small form to be filled in, such as the following:
Number of matches to bet on... a drop down menu for the user to choose from
and then for the visible area of the sheet to change to show the appropriate layout for their selection. So they'd choose the number of matches they want to bet on, and the spreadsheet would display what they need automatically.
I've looked at putting forms in and think I could do that quite easily, but how can achieve the effect of changing the layout/visible area depending on the chosen option?
I am using following code to effect a change which works fine:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Closed Flts").Unprotect "abcd"
Sheets("Faults").Unprotect "abcd"
[Code].....
But when the sheet is unprotected to effect this change the formula in cells ( row I and L) are visible to user and can delete them. Or they can even delete the cell itself or modify formula.
The formule in Row I is
Code:
=IF(A12="","",IF(G12=1,A12,IF(G12=2,A12,IF(G12=3,A12+1,IF(G12=4,A12+5,IF(G12=5,A12+28,"priority?"))))))
And in Row L is
Code:
=IF(A13="","",IF(I13
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.
How can I have a form field automatically fill some aspects of the data that's input? I've seen it before, like a phone# field automatically including the dashes.
I would like to automatically include colons in a time field. So if I were to type "081500" (or even "81500"), it would complete it as "08:15:00".