I seem to have a bug i can't quite figure out with my code - its causing excel to crash each time it reaches this line (when its commented out - the form runs fine - otherwise "excel has encountered an error")....
Set frameFullX = frameFull.Controls.Add("Forms.frame.1", "frameFull1")
frameFullX is declared as a control and frameFull is a frame already on the userform.
Anyone know what I might be doing wrong (I'm going to try restarting and see if was just an issue with my machine or not?
I have a set of userforms, two of which are big with 4 pages and many buttons, labels and textboxes that run or are involved in code being run from user input to the userform.
For some reason certain codes are cauing excel to fully crash, executing these codes will make excel crash each time and the only way to prevent it (that I have found) is to open the userform in VB editor and then close VB editor again, the code will then work perfectly for a while and then it will start crashing again (it is normally a few saves and open/closes later that this occurs).
Logically thinking, opening the userform in VB must reset something that gets corrupted or set incorrectly....
I am in the process of creating a module to validate a MulitPage UserForm that is composed of, among other controls, multiple frames, each containing a TextBox and two OptionButtons. I need to identify those frames where the TextBox value is not null and neither of the OptionButtons have been selected. The module will pass as variables the TextBox value, OptionButton captions and Frame caption to another module that will create a UserForm with the invalid Frame/TextBox/OptionButtons sets for the user to correct.
The interim code listed below runs as a command button on a test UserForm. The MsgBox displays only the first of the OptionButton’s caption as Opt1 and Opt2. Can anyone tell me why and where I went awry?
Public WithEvents As MSforms.TextBox, Opt1 As String, Opt2 As String, vtxt As Long, vFrame As String, vTag As String Private Sub CommandButton1_Click()
Dim pPage As Page, cCont As Control, vCont As MSforms.Control 'Dim Opt1 As String, Opt2 As String, vtxt As Long, vFrame As String, vTag As String
Is there a more elegant way of accomplishing this? (Control Tags are already spoken for.)
I have a Userform which has a series of Frames that contain Option Buttons. What I would like to do is check that an option button in each of the frames has been selected. If there are any missing then I need to inform the user - I would like all frames to be checked at the same time on the click of a button.
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?
I have a UserForm (UserForm61) where I have a popup calender acts like DTPicker. and some other controls . My Problem is whenever I am trying to open the calender it is opening behind every control. changing the z order played no role.
On an Excel 2003 spreadsheet I already have two option buttons. I need to add two additional optionbuttons. I placed them within a frame so that they will function independently of the other buttons. Specifically, I created Frame1, selected Frame Object-->Edit, select Toolbox, add Optionbutton3 and Optionbutton4, exit Design mode.
The new Optionbuttons correctly toggle their values when out of design mode . When I re-enter design mode and double-click to take me to the VBA code, it however only shows me Frame1_Click(). Within Frame1_Click() I have tried adding the code
Is it possible to distinguish a checkbox that is inside a frame from one that is outside it? What I am trying to do is loop through all the checkboxes on a multipage but only those that are within a specified frame.
For Each ctl in Frame1.Controls 'Do whatever Next ctl I also know you can limit the controls you diddle by doing:
For Each ctl in Frame1.Controls If TypeName(ctl) = "TextBox" Then 'Do whatever End If Next ctl But this gets absurd when it gets to the point I want it:
For Each ctl in Page1.Controls If TypeName(ctl) = "Frame" Then For Each ctl2 in ctl.Controls If TypeName(ctl2) = "OptionButton" Then ' Do whatever End If Next ctl2 End If Next ctl
Is there any way to simplify this? Can I define ctl as frame, for instance, and then it will only look for every frame object in Page1? Or is there some other trick I'm missing? Or am I just making userforms with too many wrappers?
I am developing a form for emergency dispatching. One of the features of this dispatch sheet is that there are several maps in it that open on a seperate form. The map form has 4 maps that are picture files in an image. these images are in a frame. The different images are selected using option buttons. Given the background above, my problem is this. I have code that zooms in 50% each time the Click event for the image is fired. wht i am trying to do is make the zoomed view center where i clicked. this is the code that i have so far but it doesn't work all that spectacular, the closer I zoom in the further out of center the place i clicked gets untill it is out of view.
Private Sub Layout_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) CurrentX = X CurrentY = Y End Sub
Private Sub PlantLayout_Click() If Frame1.Zoom < 400 Then Frame1.Zoom = Frame1.Zoom + 50 frmmap.Frame1.ScrollLeft = CurrentX - (Frame1.Width / 2) frmmap.Frame1.ScrollTop = CurrentY - (Frame1.Height / 2) frmmap.Repaint End Sub
I have a form that has object buttons in a frame. I see that I can only select one or the other of the object buttons that are in a frame. Is it possible to see if no object buttons were selected in a frame and then display a msgbox telling the user that they need to go back and pick a button?
Is it possible to scroll down in a UserForm Frame with a VBA Code in excel. My frame in the userform has a scroll height of 600 and a lot of option buttons. Based on a selection in a ComboBox(using If statement), I'd like to scroll down to a certain amount in the Frame.
I have a sheet with some normal cells and then some MS Forms 2.0 frames with OptionButtons inside of them. If I have OptionButtons outside of the frame I know how to read their values, but how to do it when the control is in the frame?
I need to make a form on one sheet and then make as many copies of the sheet as needed, and finally I have a macro that copies the data from all forms to one sheet, so that each form represents one row. My main goal is to save that one sheet as a .csv-file and then import it to our CMR database.
I'm not very familiar with Excel forms. Should I take some other approach to this problem or should I continue like I'm doing? I would be almost ready with my workboot if I only could read the values of the optionbuttons inside the frame.
I have tried: "If Sheets(i).FrameInstallationType.Controls.SL_STD_HD.Value = True Then" and "If Sheets(i).FrameInstallationType.SL_STD_HD.Value = True Then" where "SL_STD_HD" is the name of the OptionButton, but apparently these both are wrong.
In Excel 2000 there seems to be a problem with setting the focus in a UserForm TextBox if the SetFocus method is applied in the same submodule as the UserForm.Show method. Microsoft's circumvention for this is to put the SetFocus command in the form's Activate event submodule.
This seems to work OK except when the TextBox you are applying the SetFocus to is the first control inside a frame. It works if the TextBox is not the first control, and it can be circumvented by first setting the focus on a subsequent control then switching the focus to the intended control.
However, the circumvention is not that useful if there is only one TextBox control in a frame or, as in my current project, if you try to create a generic piece of code to validate controls from multiple forms and set the focus from within the generic code.
Does anyone know of a way over overcoming this problem and being able to directly set the focus to the first TextBox inside a frame?
On my userform I have a frame. Within the frame, there are 6 option buttons. Can I trigger an event when when the user selects a different option button? I was expecting to see a Frame.Change method. But I don't. One solution is to insert code on each of the Option Buttons Change methods.
The code below works really great which Andy helped me with, but now I've put the textboxes that are changing in a frame. That is because I need to scroll the textboxes. That works in another form I've got, but for this form I've also got the textbox, TBSum601, which is updated from the sub UpdateTotal() (see below). BSum601 is placed on the form but not inside the frame. When I run the application it stops in Sub TxtGroup_Change() at the line: TxtGroup.Parent.UpdateTotal I think it's because of that the textboxes for the TxtGroup now are in a frame instead of as before, on the form. don't know exactly what the "Parent" does, but I think it's something I have to do with the code there.
Originally Posted by Andy Pope Class event VBA: Private Sub TxtGroup_Change() If Me.TxtGroup.Text = "" Then Me.TxtGroup.Text = 0 End If TxtGroup.Parent.UpdateTotal End Sub
Public Sub UpdateTotal() Dim lngTotal As Long Dim lngIndex As Long For lngIndex = 1 To UBound(X) / 4 ..................
why the following code generates an error if I use the backspace key to clear the textbox?
Code: Dim t As Date If Len(Controls.Item("txtstart").Value) = 4 Then On Error Resume Next t = TimeSerial(Left(Controls.Item("txtstart").Value, 2), Right(Controls.Item("txtstart").Value, 2), 0) On Error GoTo 0 ElseIf Len(Controls.Item("txtstart").Value) = 5 Then On Error Resume Next
I have a piece of code in one of my Macro's, if I run the macro and don't let this piece of code run everything works fine, when I let this piece of code execute the excel file keeps running until I go to save, at which point I get a message saying "Microsoft office excel has encountered a problem and needs to close. We are sorry for any inconvenience".
Code is as follows:
If MsgBox("Copy and hardcode this scenario?", vbYesNo) = vbYes Then Calculate Application.DisplayAlerts = False
I'm testing some userform code & it seems to cause some sort of lockup where I can't enter anything into the excel cells. It doesn't just affect the spreadsheet the userform is attached to but any spreadsheet that I have open.
I have placed a Frame Control on Excel Sheet and add Some Controls(TextBoxes) to it. What I want to Do is when I enter Value in TextBox1 and the Focus Got to TextBox2
Then Value from TextBox1 will place on Activesheet.cells(1,1)..
Please check the Attached File : FrameControls.xlsm
In the attached on the quote tab in cells G5 & H5 i have two formulas both trying to pick up the data from the highlighted matrix in the frame tab.(i only need one)
From the dropdowns in cells F1, F3 & F5 in the quote tab I wish to get the data from the matrix in the frame tab cell range C3 - F20
I'm trying to Automatically fit the text to the size of the TextBox frame using:
Code:
With obFinalNote.TextFrame2 strTxt = .TextRange .DeleteText .WordWrap = msoTrue .AutoSize = msoAutoSizeTextToFitShape .TextRange = strTxt End With
It appears ".AutoSize = msoAutoSizeTextToFitShape" only considers the width (I think) as the text is downsized but yet the text still extends beyond the the bottom of the frame. And never gets set to less than 11 point. Even when there's not enough text to reach the bottom of the frame the text is still set to 11pt leaving the frame half empty. Need to have text big as possible.
How to keep text in its entirety within the frame of the shape?
The quantity of text varies widely and the frame size cannot change. I've tried using Len() and dividing by a certain number and then based on that answer set the size with:
Code:
With obFinalNote.TextFrame.Characters.Font .Name = "Arial" .FontStyle = "Normal" .Size = NoteFontSize .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With
But this technique doesn't seem reliable as the number to divide the Len() by seems to depend on how "wraps" have occured.
I can't refresh the values in the captions in the labels that have been dynamically added to Frame 1 on page 1 of a multipage control. I have added the salesperson' name with a checkbox, and three labels for each sales person. The captions for the labels are the values for the number of sales, gross sales amount and commission. The captions get these values from an Array "SalesAry()".
Outside the multipage control I have another frame with the total of salesmen, service, product, customers, total sales, total commission total product sold. The user can check the checkboxes next to each sales person to include in the final report. After the user checks some of the checkboxes, there is an "Update Totals" Button on the userform (outside the multipage) to update the totals.
This works, but the values in the sales person's labels do not update. I have tried to repaint the userform me.repaint or me.frame1.repaint but the labels in the frames on pages do not repaint or refresh?? I have also tried to focus the userform to the multipage by inputting "multipage1.value = 0" just prior to me.repaint. I have three other pages on the multipage. Same issue/problem. Page2 is the Service person their number of accounts, gross sales and commission, Page 3 is the Products names Number sold, gross sales and commission and page four is the Customer name number sold and the commission.
In the end I want the user to select (check) only the salespeople, Service person, Products and Customers to include on the report. But since I can't get the labels in the frames in the multipage to update, it just looks confusing. I know that the Arrays are updating because the Totals keep getting updated correctly based on the selections, but again it looks confusing because the total don't match the values in the labels?
I want to write a VBA code that will place ActiveX Frame directly onto the worksheet and then add 3 Option Buttons from Toolbox into that frame.
I could not find an answer searching through the web... Also when trying to record macro and see the code it did not record placing the buttons from the toolbox or any formatting (color, caption, etc.) via properties...
When formatting the buttons it would be useful if the selected radio button would have different color, bold font, etc.
I need to be able to calculate the actual number of days worked for an employee over a specific date range. I have a data sheet containing employee, transactions, and date of transaction (date only, does not include time). The actual number of days worked may fluctuate due to time off or holidays, so I need to be able to calculate this individually by employee. For example, with columns Employee, Transaction, & Transaction Date:
I have a nifty little form that I created that list some bullet points (as Labels) along the left side of the page, and clicking on these Labels run code that hooks up to a VLookup on the worksheet and displays custom text in a larger text box on the right.
Then I added a Frame with three option buttons, a text box and a command button to the bottom of the form. And now, clicking on the Labels triggers an error message:
"Object doesn't support this property or method."
But if I remove the frame, the code works fine... The code line that bugs says:
I am trying to do a timesheet spreadsheets that lists employees clockin and clockout times
Name Start End num hours worked Cory 02:00 04:00 2 Jack 23:00 05:00 6 Fred 10:00 17:00 7
and then go through the list and and count the number of employees in a certain range.
time range number of employees working 05:00-06:00 1 06:00-07:00 2 07:00-08:00 6 08:00-09:00 5 09:00-10:00 10:00-11:00 11:00-12:00 12:00-13:00..............
