Can't Refresh Captions On Labels In Frame On Multipage
Mar 10, 2014
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?
View 5 Replies
ADVERTISEMENT
Dec 3, 2006
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.)
View 4 Replies
View Related
Jan 30, 2009
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?
Validate UserForm MultiPage and Frame Controls
View 3 Replies
View Related
Feb 7, 2014
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1
2
3
4
Here's what each of those "mean" (I have this in another table):
1 - Sick
2 - Overtime
3 - Court
4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
View 8 Replies
View Related
Mar 11, 2009
I have a UserForm and what I'm trying too do is change the color property of all the labels on the form simultaneously.
View 9 Replies
View Related
Jan 16, 2014
I have a worksheet that is just a list of items with a location listed next to each item. I took that list and made it into a pivot table and would like it to have it list the items with each location it is in displayed across. I have already set it to tabular form in the pivot table options, but if there are multiple locations it lists them vertically and I want them list horizontally for printing purposes. I have attached a screen shot to explain.
View 1 Replies
View Related
Nov 8, 2008
On a user form I have added a multipage Object. It has 5 pages. How can I have each page caption read "Name" Comments where "name" is pulled from a worksheet field (say Questions!C2).
So the Caption needs to pull from a field and be concatenated with the word "Comments"
View 5 Replies
View Related
Dec 23, 2011
I have a userform with 70 toggle buttons, the buttons are arranged in 7 columns and 10 rows, hence I named each button c1r1 to c7r10.
The button captions will change, so I want to take the captions from cell contents: worsheet 'Buttons' and the range A1:A70
I can do this long hand...
c1r1.Caption = Sheets("Buttons").Range("A1")
c1r2.Caption = Sheets("Buttons").Range("A2")
.....
c1r10.Caption = Sheets("Buttons").Range("A10")
c2r1.Caption = Sheets("Buttons").Range("A11")
c2r2.Caption = Sheets("Buttons").Range("A12")
unitl I get to
c7r10.Caption = Sheets("Buttons").Range("A70")
I guess a loop would be better. Here's my code that does not work..
I was just going to insert word 'works' for now until I get the loop to work.
I'd also have to get a loop inside this loop, ie when the column number is 1, it needs to loop through the rows numbers, before it loops to column 2.
I guess I might be able to do that, but I fall at the first hurdle, I get Error 424, object required. I guess it does not like 'buttonaddress.caption'
Do I need to declare or dim 'buttonadress'?
buttoncoladdress = 1
buttonrowaddress = 1
Do Until buttoncoladdress = 7
buttonaddress = "c" & buttoncoladdress & "r" & buttonrowaddress
buttonaddress.Caption = "works"
buttoncoladdress = buttoncoladdress + 1
Loop
View 3 Replies
View Related
Jan 21, 2013
I have problem with changing multiple captions on pivot table.
I am using olap cube and I want to update all my "product code" captions on pivot table. Can it be done with VBA code?
I have old code-new code table and I want to replace the old code captions with new ones, copy paste does not work on pivot tables, and formulas are also not available, but is there a way to change the captions with VBA code?
View 7 Replies
View Related
Feb 23, 2010
I have create a Command Bar, my problem I'm trying to understand is this
1.Can you amend the font colour of the any Captions on the CommandBar?
2.How can I get the Caption to pick up from a range and use the range name as the Caption?
I have named a range in a worksheet that I want to pick up and use as the range. Below is my CommandBar code
View 2 Replies
View Related
Aug 23, 2006
I have an excel application that I'm developing that is going to be used by two different departments. On one side everything will pretty much be userforms. The sheets they the data I need to pull in for them is on sheets that I have such that their visibilty is equal too xlveryhidden. I'm trying to change the captions on labels using the data on the veryhidden sheets. Something like this.
'frmCurrentProposal is a userform
'lbGroupName and lbGroupNum are both labels that should vary
frmCurrentProposal.Show
frmCurrentProposal.lbGroupName.Caption = Sheet2. Range("B2").Value
frmCurrentProposal.lbGroupNum.Caption = "Group # " & Sheet2.Range("B3").Value
The labels are not changing their values.
View 8 Replies
View Related
Jan 4, 2008
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
View 9 Replies
View Related
Feb 6, 2013
I have a userform with 17 checkboxes that their captions should correspond with column A range ("A2:A18) on a worksheet.
Is there a way in userform_initialize, or activate, to create the captions from that range?
I could type the code 17 times like;
Code:
me.checkbox1.caption = wb.worksheets("area").range("a2").value
I was curious to know if it could be looped? Textboxes on the same userform.
View 6 Replies
View Related
Oct 1, 2006
how to change in a for next loop the CommandButton.caption in a usersform
For n=1 To 300
CommandButton & n.caption= Sheets("info"). cells(n,1)
Next n
View 9 Replies
View Related
Jul 27, 2006
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
View 4 Replies
View Related
Jan 13, 2009
I've got a spreadsheet with a few queries to extract data into my sheet. Whenever I open the spreadsheet, I get a "query refresh" dialog box, asking if I want to "Enable automatic refresh". I keep clicking on the "Enable" button but I have to keep answering the question for each open. Isn't there a way to set "yean - ok - refresh the data" so that I don't have to keep saying "yes"?
View 2 Replies
View Related
Nov 27, 2007
Will someone please tell me the difference (if there is a difference) between the following 2 lines of ....
View 6 Replies
View Related
Jun 25, 2014
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.
View 9 Replies
View Related
Sep 2, 2009
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
View 5 Replies
View Related
May 16, 2011
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.
View 4 Replies
View Related
Jan 30, 2009
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?
View 9 Replies
View Related
Jul 15, 2006
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
View 2 Replies
View Related
Feb 3, 2009
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?
View 4 Replies
View Related
Jan 18, 2010
I have a formula that calculates only seconds and frames. The frame rate is 75 fps (0-74).
e.g., 49.50 is 49 seconds 50 frames. It will not parse Minutes.Seconds.Frames, e.g., 1.49.50 is 1 minute 49 seconds 50 frames.
What my existing formula does is converts the number to all frames then converts the answer back to seconds and frames.
I need this formula to include minutes in its calculation.
A copy of the spreadsheet is here: ...
View 14 Replies
View Related
Feb 28, 2010
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.
View 7 Replies
View Related
Jun 25, 2013
How to create repeated time frame within a column like example stated as below:
Time 9:45AM
10:00AM
10:15AM
9:45 AM
10:00AM
10:15AM
9:45 AM
10:00AM
10:15AM
View 1 Replies
View Related
Mar 3, 2008
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?
View 6 Replies
View Related
Nov 20, 2009
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.
View 9 Replies
View Related
May 9, 2005
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?
View 9 Replies
View Related
Nov 10, 2006
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.
View 2 Replies
View Related
