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 RepliesI have a setup at the moment where I have a spreadsheet using a bunch of macros that are coded in a module attached to another spreadsheet. In both spreadsheets the macros are tied to Excel buttons placed within worksheets. When I copy the spreadsheets each month to new directories - they're used for some monthly reporting - I see the following behavior:
1/ The spreadsheet that contains the module with the code for the macros correctly updates the location of the macros and works OK.
2/ If I open the other spreadsheet and save it the macros appear in the tools/macro dialog as having changed location OK. However, if I try to use the macros by pressing a button the macros invoked are in the old location. If I check the assignment of the macros in this spreadsheet by right-clicking on a button, indeed the macros invoked are in the old location. This means that I have to go through and manually update for each button the macro invoked.
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.
How can data be input through option buttons, textbox, listbox, combos, checkbox in web pages organised in frames?
View 3 Replies View RelatedIs it possible to have a MsgBox that would say "Hello" to the user who is logged in on that computer. How would you reference the user info?
View 4 Replies View RelatedIn the link below, the video shows you can link a photo to a dropdown box. Is it possible to link 2 photos to 1 dropdown box?
[URL] ....
I am trying to get an Index-Match formula to search a different sheet depending on the value selected in a particular cell using a dropdown list. See the attached workbook.
View 3 Replies View RelatedI have 60 days of data, Nov 1st through Dec 31st.
The date is my x-axis labels.
I produce a daily report. I'd always like for the current dates vertical bar to be "red".
Is there any easy way to have this accomplished?
I want to insert a formula that is tied to a specific column. I know how to do formulas and have a slight understanding of macros. Can I insert the formula as part of a larger macro?
View 3 Replies View RelatedThe idea of getting a scatter chart to have a smooth spectrum from http://www.ozgrid.com/Excel/xy-scatter-color.htm is brilliant, but there is a slight problem I am having with it. The number of points possible in the chart determines the color, rather than the values of cells. I have a table with 100 rows and the chart is based off data from that table; however, instead of using cell references in SERIES, I have named ranges, as part of the table returns a shortened list of values based on user-selected parameters.
In short, this means that if only 7 values are returned in this shortened table, the coloration sees 7 values out of 100 and goes only 7/100ths of the way from red to green. Is there a way to get the coloration based on a certain value in the worksheet for that row? For example, if I have a list of test scores ranging from 0% to 100%, can I get the same coloration to show for a score of 88%, regardless of whether one record is selected or all of them?
Also, if I wanted a user to be able to easily change the parameters of the spectrum so that they could see a spectrum of scores from 50% to 100%, can I get the macro to look these values up from the worksheet?
There are two modules in what I am using now, the class module is called CSpectrum and reads:....
i have product group,product name and the statistics. I'll use an example of students with score. I have these set of data:
A B C D
1 Student Name Score Sum
2 Student1 Anna 48 80=Sumif(A:A,A2,C:C)
3 Student1 Anna 32 80
4 Student2 Tom 30 80
5 Student2 Tom 30 80
6 Student2 Tom 20 80
7 Student3 June 55 60
8 Student3 June 1 60
9 Student3 June 4 60
Now we have 2 students with tied highest scores with 80 Scores. Naturally i want the rank to be as follow: Top1 Anna since she has the highest score "48", and Top2 Tom, and Top3 June.
The problem is, the score data can vary and Top3 can probably have the highest score and he still didnt make the highest score collectively. And there will also going to be other situations as well because im working on a very large data set, and not these 3 students.
the max score is tied, and since im making another column with sum scores, the data is going to be redundant, and hopefully theres a formula to ignore this.
I am trying to create a worksheet that has multiple radio buttons. Each radio button is linked to a Macro. I need there to be many buttons running down one side of the worksheet. Each button needs to perform a macro that is relevant to the cells in the same row that it is on.
Essentially what i am trying to do is make a macro that when the button is pressed copys data from H6 and paste it into B6. The button is situated above I6. I need a button for each row from 6 to 110.
While this macro is easy to create (i use the record button and then assign the macro to the button), i would have to do this 104 times and assign a new macro to each button.
Is there a quicker way?
Is it possible to change the color of buttons or command buttons? There does not seem to be any place that allows this under properties for buttons, although there does for command bars. However, I've tried recording a macro as I change the color, but nothing get's recorded so I'm not sure what the syntax would be.
I have a spreadsheet with several buttons and I'd like them to change colors as they are pressed so it's possible to see what you've already done. And then, as soon as any other cell on the sheet is changed, the buttons reset color.
I'm getting the following error:
"Object library invalid or contains references to object definitions that could not be found"
I wasn't getting that error last night and I'm not sure what I may have done to cause this error.
It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.
I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.
Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?
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 RelatedOn 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.
View 4 Replies View RelatedFor 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 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: ...
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 RelatedHow 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
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 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.
View 2 Replies View RelatedThe 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 ..................
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.