How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
I am creating a bar inventory/"numbers" sheet for a corporation. I have been searching on and off for a week or so to find an answer or a tutorial on what I am looking to implement into this worksheet. I want to take a userform, with a multipage control, and have the control for each tab, show me a different part of the worksheets. For instance, the first tab would be inventory, the second tab would be ordering, the third tab would be weekly numbers, etc ... I am wondering how to "add ranges to the multipage control in order to make it a "viewer". I have found tutorials on how to print, enter info into the form and save it to the sheets using a button, but I can't find a tutorial on how to implement the control on how to make tabs show the ranges. Can someone point me in the right direction on a tutorial, or maybe if willing a small spreadsheet with a mulitpage control on it, showing how to add the ranges from different worksheets?
UserForm1, page5. From Combobox1 I choose a city and the textboxes give me the values from Sheet3. I'd like to color in green the smallest value. for example, if for LHR I find for A-4214, B-4420, C-3127 => value 3127 to be green. The problem is I have more textboxes in other pages and some of them are colored in green also. Is possible to reffer to these textboxes from page5 only?
I'm using a multipage control on a form that has the style set to fmTabStyleButtons. So it uses buttons instead of tabs. I named one of my buttons (tabs) to Close. Can I set this up to where just pressing the Close button will cause it to exit the app not just go to that page?
i am trying to make the multipage form so that page 2 ,3 is password protected intially the form will open read only, with access to subsequent pages through a password.
Last weeks I've had too few spare moments to answer questions, hope to pick that up soon. I have a strange problem now with some checkboxes on a multipage control.
Please see the attachment.
I want to check all the 5 boxes on a page if the box above is checked, and vice versa, uncheck them all if the box above is unchecked. I use code of Dave Hawley to loop through the checkboxes.
This doesn't work, but oddly enough, it works if I insert a MsgBox in the code.
I have a DTPicker on a multipage form on a userform with the following codes:
sheet1. range("Ac2").value=DTPicker1.value
DTPicker1.value=sheet1.range("AC2").value
The first line of code is in the DTPicker1_change event.
The second line of code is in the userform_initialize event.
This works fine if I move the DTPicker control onto the userform itself, but if I put the control on the multipage form it fails with the following message: Run Time Error 35788
An error occurred in a call to the Windows date & time picker control.
I have a userform with a multipage issue. On page1 I have a command button that formats cells. On page2 I have textbox1 with a fixed number, textbox2 that counts the number of formatted cells (from the command button on page 1) and textbox3 that's going to sum textbox1 and 2. So here's the issue. Lets say I'm on page2 viewing the textboxes, if I switch back to page1 and format additional cells and then switch back to page2, textbox2 does not update with the additional cells formatted. I always need to close the userform and reopen it for the changes to take place.
I've started experiencing a strange problem. The control toolbox keeps appearing when the first page on a multipage userform is activated. I can't figure out what's causing this, and after searching the forum, with no luck, I thought I would see if anybody else is experiencing the same problem, and how to fix it.
I have a userform which has a number of multipage controls nested inside each. So, the parent is Multipage1 with 2 pages. Page 1 of Multipage1 has a second multipage control (Multipage2 which has 4 pages). In Multipage2, page1 there is a 3rd Multipage control (Multipage3 which has 2 pages). In Multipage2, page 2 there is another multipage control (Multipage4 which has 3 pages)...and so on! I know this sounds complicated, but I am trying to get the caption of the selected multipage.
There are commandbuttons in all of the pages, which when clicked need to return the caption name of the multipage where they sit.
I have started the following code, but suspect there must be a better solution:
VB:
With Sales If .MultiPage1.Value = 0 Then Hardware_Purchases_Input.TextBox6.Value = .MultiPage1.SelectedItem.Caption & " - " & .MultiPage2.SelectedItem.Caption & " - " & .MultiPage3.SelectedItem.Caption End If End With
I want to access the Properties of a number of controls in a running form, and these controls may or may not be contained in a Frame or a MultiPage.
In particular I want the Top and Left for these controls, which means I have to first find out if the control is contained in a Frame or MultiPage so I can get the reference for Top and Left. I'm ok with doing this for controls inside a Frame, but the MultiPage is eluding me. I get an error when I try to access these controls and it looks like they are actually owned by the individual Pages of the MultiPage.
how do I find out if a given control is contained in a given MultiPage?
how I would display a simple msgbox to a user if a date they select from monthview isn't a date I want them to select. They can only chose dates between aug and dec 07 and i have the code lines
If MonthView1.Month = "12" And MonthView1.day = "15" Then december15.december15
I have a user form that has a calendar button. Once clicked it updates a txtbox on my user form. What I would also like is for another txtbox (txtQtrLeave) to be auto populated based on this entry. Below is the code I've tried:
If CalendarEnd.Calendar1.Value > #3/31/2008# And CalendarEnd.Calendar1.Value < #1/6/2008# Then Qtr = 1 frmAddClient.txtQtrLeave.Value = Qtr
... Qtr 2, 3 etc
It seems to return a value if I have one criteria but it doesn't work once I add the And element. What am I doing wrong (this has been driving me mad all morning)?
I have a worksheet that contains a number of ActiveX image controls, which have been scaled to a uniform height to serve as thumbnails.
I have also created a class, where each instance is initialized with one of these image controls, and events (specifically the on click event) are captured. When the user clicks the picture, it opens a userform containg the same picture. However, I'd like that picture to be at its original size, not the scaled-down size.
how can I determine the original dimensions of a picture used in an image control? I can tell that the original resolution is there (by changing the PictureSizeMode to clip), but I can't find any properties that show me how big that image actually is, only ones that return or set the size of the object itself.
Having created a spreadsheet with active x calendar control I find that if the worksheet is password protected I'm unable to use the control to enter a date. Take the password off and it works fine. Is there a way around this?? The password for the attached example is "date"
I have created a spreadsheet that uses checkboxes. When deleting a particular row that is no longer needed, it leaves the check boxes. Is there a way to link them to the cell (which contain formulas) so when the row is deleted, I don't have to delete each individual check box.
I have joined out of desperation relating to a problem with an Active X control inserted into a userform. This control accesses and returns data from an instrument on the RS232 PC comms port. All works well until I try to save, copy or print the workbook using usual VBA code, then mysteriously the code just stops, and Excel needs to be restarted again. It seems to just get lost!
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
I have created a user form and I am trying to get the active control colored. I am using the code from Mr Excel's VBA book - starts on page 454, and I have entered all the code, but now when I activate the user form I get an error ....."Compile Error. Invalid attribute in Sub or Function." and when I click OK, it takes me to this line of code.....
Private WithEvents objForm As clsCtlColor
In the book, this is where I am supposed to start entering the code "behind the userform" rather than in the class module. So, I assume this means that this code goes with all the other code for the user form (in VBA project click on form, then view code). Am I wrong? Should I be adding a module? Not sure what I am doing wrong.
Here is the code I have in the class module....
Public Event GetFocus() Public Event LostFocus(ByVal strCtrl As String) Private strPreCtr As String
Public Sub CheckActiveCtrl(objForm As MSForms.UserForm).......
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
is it possible to choose an option button which will then perform a calculation on a cell reference? I’m trying to deduct a percentage from a total when an option button is highlighted.
I'm having some trouble getting control ENTER & EXIT events to fire properly when having controls embedded on frames within a userform. I'm using Excel 2003, 2007, & 2010. Here's the userforms I'm working with:
With FRAME:
Without FRAME:
In both cases, the DESCRIPTION field is disabled. The selectable controls on both are a combo-box, textbox, listbox, & 2 buttons. On the FRAMED version, the combo-box & textbox are contained on a FRAME.
Here's the code, same on both userforms:
Code: Option Explicit Private Sub cmbRecipes_Enter() ListBox1.AddItem "ENTER - " & cmbRecipes.Value End Sub Private Sub cmbRecipes_Exit(ByVal Cancel As MSForms.ReturnBoolean) ListBox1.AddItem "EXIT - " & cmbRecipes.Value End Sub
All this is doing is posting a message to the listbox when the combo-box ENTER & EXIT events fire. This works as expected without the FRAME, ENTER is shown when the combo-box is entered and EXIT is shown as focus is moved to another control. But when running it on the FRAMED version all I get is a single ENTER event recorded regardless of how I move the focus through the control set.
Another oddity is that if I have more than 1 control that can receive focus on the FRAMED version, it appears to work correctly.
I have created an excel worksheet that will provide budgeting and estimating tools for my project managers. All data used to be manual entry and took a good while to complete. I am trying to automate the process with VBA.
I created a UserForm called InfoVerify1. On that form I have TextBox 1 - 10. When the UF opens, the boxes display project information from my worksheet called "Basis of Estimate", also known as Sheet26.
The TextBox1 ControlSource is set to "E4". When I run the macro with Sheet26 active, the proper information fills in. However, when I am on the Start page or any other worksheet and I run the macro, it tries to fill in the text boxes with E4, etc, from the active sheet. I tried changing the ControlSource to "Sheet26,E4" or any combo thereof with only error messages.
how to get it to refer to a cell on a particular worksheet and hold to that worksheet no matter which sheet I am on at the time I run the Userform?
Has anyone out their ever seen an Calendar type of control totally built in an Excel vba UserForm?
My problem that I’ve tried to resolve for some time is utilizing some type of pop-up calendar to eliminate format issues in my published Excel forms. I have tried a number of calendar controls but all have to be registered on the local machine and this cannot be guarantied for every machine.
If someone could direct me to a vba UserForm that has this built in that might do the trick. Or is their another way to deal with this?
I have 2 macros: 1 controlled by a checkbox activex (PA_03) and the other a combobox (PA_03_rows) in a worksheet. I display the results from PA_03, and the user than then increase or decrease the number by using the combobox. But when I change the value (PA_03_rows.value = x) it causes the macro to jump to the macro.
I don't think it did initially, but it does now. According to another post, I saw it shouldn't do it! Is there something I'm just not seeing here?
Private Sub PA_03_Click() Dim message, title, default, numberRows Dim PA_rows As Integer Application. ScreenUpdating = False TakeFocusOnClick = False Worksheets("sheet1").Select ActiveSheet. Range("a15").Select If PA_03.Value = True Then Goto Unhide: If PA_03.Value = False Then Goto Hide:
Unhide: message = "Enter the number of input rows required (1 to 50)" title = "Non-Featured Standard Input" default = "1"
I have added a control (a check box), it has called itself "Check Box 1", can I change it's name? Reason I want to do this is that I am using a macro to clear the check boxes, and therefore using a loop and I want the next control to be 5, not 12.
