Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frame as variable (newbie question)
Hello all,
I'm using Excel 2000. I have a form with 7 frames, each with 10 comboboxes. I can loop through all the boxes on each frame if I hardcode it. for each control in userform1.frame1.controls... I'd like to be able to change frame1 to a variable so I only have to use one sub as opposed to 7. dim x as ??? ?set x = combobox1.parent? for each control in userform1.x.controls... Basically, I've written a sub that, on the combobox change event, will alert the user if the same value is in more than one combobox in the same frame. I don't want to prevent duplication, just let the user know with a msgbox. The sub is written & works fine, I just can't make it work for more than one frame. My plan is to declare a public variable, and set it's value in the change event proc. If my idea isn't possible, how can I find out what frame the combobox that changed is in? Thanks in advance, Rik |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frame as variable (newbie question)
Hi Rik,
I have a simple idea. Why don't you name for each controls depending on the name of the frame? For example, assume you have 2 frames named Frm1 and Frm2 on the userform. On Frm1, place 2 TextBox controls as Frm1_TextBox1 and Frm1_TextBox2. On Frm2, place 2 TextBox controls as Frm2_TextBox1 and Frm2_TextBox2. So, you can use one sub as opposed above 2 Frames using an argument. Please have a look at the following procedure. Private Sub CommandButton1_Click() InputValue 1 End Sub Private Sub CommandButton2_Click() InputValue 2 End Sub Private Sub InputValue(ByVal FrmIndex As Long) Dim strFrmPrf As String strFrmPrf = "Frm" & FrmIndex & "_" Me.Controls(strFrmPrf & "TextBox1").Text = "Hey" Me.Controls(strFrmPrf & "TextBox2").Text = "What's Up" End Sub HTH Colo -----Original Message----- Hello all, I'm using Excel 2000. I have a form with 7 frames, each with 10 comboboxes. I can loop through all the boxes on each frame if I hardcode it. for each control in userform1.frame1.controls... I'd like to be able to change frame1 to a variable so I only have to use one sub as opposed to 7. dim x as ??? ?set x = combobox1.parent? for each control in userform1.x.controls... Basically, I've written a sub that, on the combobox change event, will alert the user if the same value is in more than one combobox in the same frame. I don't want to prevent duplication, just let the user know with a msgbox. The sub is written & works fine, I just can't make it work for more than one frame. My plan is to declare a public variable, and set it's value in the change event proc. If my idea isn't possible, how can I find out what frame the combobox that changed is in? Thanks in advance, Rik . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frame as variable (newbie question)
Or....
Private Sub UserForm_Click() Dim ctl As Control Dim ctl2 As Control Dim Fr As Frame For Each ctl In Me.Controls If TypeName(ctl) = "Frame" Then Set Fr = ctl For Each ctl2 In Fr.Controls MsgBox Fr.Name & " " & ctl2.Name Next End If Next End Sub Alex J "Rik Smith" <Rik wrote in message ... Hello all, I'm using Excel 2000. I have a form with 7 frames, each with 10 comboboxes. I can loop through all the boxes on each frame if I hardcode it. for each control in userform1.frame1.controls... I'd like to be able to change frame1 to a variable so I only have to use one sub as opposed to 7. dim x as ??? ?set x = combobox1.parent? for each control in userform1.x.controls... Basically, I've written a sub that, on the combobox change event, will alert the user if the same value is in more than one combobox in the same frame. I don't want to prevent duplication, just let the user know with a msgbox. The sub is written & works fine, I just can't make it work for more than one frame. My plan is to declare a public variable, and set it's value in the change event proc. If my idea isn't possible, how can I find out what frame the combobox that changed is in? Thanks in advance, Rik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Frame Reference | New Users to Excel | |||
Real Newbie newbie question | New Users to Excel | |||
How to attach legend frame to chart frame? | Charts and Charting in Excel | |||
VBA Question - How to print a frame within a template | Excel Programming | |||
VBA Question - How to print a frame within a template | Excel Programming |