Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
check ocontrols within frames within userform
dear gurus -
i'm in waaaay over my head. i THINK i understand this theory...... to explain: i have a userform w/a 3-page multipage. each multipage has (approx.) 4 frames on it each frame has 3-5 option buttons in it. the purpose of the userform is to incrementally add "1" to each evaluation question on the worksheet. Question 1 (frame name is Q1) 5 (optionbutton name is Q1Opt5) 4 (optionbutton name is Q1Opt4) 3 (name is Q1Opt3) Question 2 (frame name is Q2) 5 (name is Q2Opt5) 4 (name is Q2Opt4) 3 (name is Q2Opt3) and so forth. the worksheet ranges correspond: Set r1 = ws.Range("d8:j13") Set r2 = ws.Range("d18:j23") Set r3 = ws.Range("d28:j33") so, i want to go thru each frame, find the option button that is selected, find THAT # option button in the corresponding range, find the correct row, and add 1 to the correct column in the correct row in the correct range. i'm using iCtr as the numeral for each frame. this is what i've got so far.............. Dim iCtr As Long 'this is how i can incrementally check each frame For iCtr = 1 To 13 If TypeOf oControl Is msforms.Frame Then oControl = "Frame" & iCtr 'now i need to see which option button = true within each frame... For Each oControl In oControl If oControl("Q" & iCtr & "Opt1").Value = True Then sRange = "r" & iCtr sCaption = oControl("Q" & iCtr & "Opt1").Caption 'the captions of each option button are 1, 2, 3, etc. Set rFound = myRange.Find(What:=sCaption, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If rFound Is Nothing Then MsgBox "Caption not found in range" End If myRow = rFound.Row 'column i contains the numeric that needs incrementing Set myRange = ws.Range("i" & myRow) myRange.Value = myRange.Value + 1 'example: Range("C2") = 26 'myRange("C2").value (26) = 26 + 1 End If Next oControl End If Next iCtr as i usually do, i suspect this is coded way more difficult than it needs to be. any help or suggestions? thank you! susan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
check ocontrols within frames within userform
once more, with emphasis............ & then i'll leave you alone!
:) susan On Mar 19, 9:02 am, "Susan" wrote: dear gurus - i'm in waaaay over my head. i THINK i understand this theory...... to explain: i have a userform w/a 3-page multipage. each multipage has (approx.) 4 frames on it each frame has 3-5 option buttons in it. the purpose of the userform is to incrementally add "1" to each evaluation question on the worksheet. Question 1 (frame name is Q1) 5 (optionbutton name is Q1Opt5) 4 (optionbutton name is Q1Opt4) 3 (name is Q1Opt3) Question 2 (frame name is Q2) 5 (name is Q2Opt5) 4 (name is Q2Opt4) 3 (name is Q2Opt3) and so forth. the worksheet ranges correspond: Set r1 = ws.Range("d8:j13") Set r2 = ws.Range("d18:j23") Set r3 = ws.Range("d28:j33") so, i want to go thru each frame, find the option button that is selected, find THAT # option button in the corresponding range, find the correct row, and add 1 to the correct column in the correct row in the correct range. i'm using iCtr as the numeral for each frame. this is what i've got so far.............. Dim iCtr As Long 'this is how i can incrementally check each frame For iCtr = 1 To 13 If TypeOf oControl Is msforms.Frame Then oControl = "Frame" & iCtr 'now i need to see which option button = true within each frame... For Each oControl In oControl If oControl("Q" & iCtr & "Opt1").Value = True Then sRange = "r" & iCtr sCaption = oControl("Q" & iCtr & "Opt1").Caption 'the captions of each option button are 1, 2, 3, etc. Set rFound = myRange.Find(What:=sCaption, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If rFound Is Nothing Then MsgBox "Caption not found in range" End If myRow = rFound.Row 'column i contains the numeric that needs incrementing Set myRange = ws.Range("i" & myRow) myRange.Value = myRange.Value + 1 'example: Range("C2") = 26 'myRange("C2").value (26) = 26 + 1 End If Next oControl End If Next iCtr as i usually do, i suspect this is coded way more difficult than it needs to be. any help or suggestions? thank you! susan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
check ocontrols within frames within userform
The following works for 2 Frames with 4 Option Buttons in each. Each
Option Button has it's Groupname property set to "Frame1" or "Frame2". You probably want to put the code in a different Sub. Hth, Merjet Private Sub UserForm_Click() Dim iCtr As Long Dim oControl As Control Dim ws As Worksheet Dim myRange As Range Dim rFound As Range Dim sCaption As String Set ws = Sheets("Sheet2") 'this is how i can incrementally check each frame For iCtr = 1 To 2 For Each oControl In Me.Controls If TypeName(oControl) = "OptionButton" Then If oControl.GroupName = "Frame" & iCtr Then 'now i need to see which option button = true within each frame... If oControl.Value = True Then Set myRange = ws.Range("d8:j23") sCaption = oControl.Caption 'the captions of each option button are 1, 2, 3, etc. Set rFound = myRange.Find(What:=sCaption, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If rFound Is Nothing Then MsgBox "Caption not found in range" End If 'column i contains the numeric that needs incrementing ws.Range("i" & rFound.Row) = ws.Range("i" & rFound.Row) + 1 End If End If End If Next oControl Next iCtr End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
check ocontrols within frames within userform
thank you merjet, i will give it a try!
looks like i was close, but i couldn't get my brain around it. i'll let you know how it turns out. susan On Mar 19, 1:44 pm, "merjet" wrote: The following works for 2 Frames with 4 Option Buttons in each. Each Option Button has it's Groupname property set to "Frame1" or "Frame2". You probably want to put the code in a different Sub. Hth, Merjet Private Sub UserForm_Click() Dim iCtr As Long Dim oControl As Control Dim ws As Worksheet Dim myRange As Range Dim rFound As Range Dim sCaption As String Set ws = Sheets("Sheet2") 'this is how i can incrementally check each frame For iCtr = 1 To 2 For Each oControl In Me.Controls If TypeName(oControl) = "OptionButton" Then If oControl.GroupName = "Frame" & iCtr Then 'now i need to see which option button = true within each frame... If oControl.Value = True Then Set myRange = ws.Range("d8:j23") sCaption = oControl.Caption 'the captions of each option button are 1, 2, 3, etc. Set rFound = myRange.Find(What:=sCaption, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If rFound Is Nothing Then MsgBox "Caption not found in range" End If 'column i contains the numeric that needs incrementing ws.Range("i" & rFound.Row) = ws.Range("i" & rFound.Row) + 1 End If End If End If Next oControl Next iCtr End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
check ocontrols within frames within userform
ok, one hang up on this line:
Set myRange = ws.Range("d8:j23") this range has to be able to increment thru each question's range using the frame's iCtr number. if i set it as a static range, then it won't move down for each question. but if i try to set the range as: Set myRange = "r" & iCtr where i've already set each individual range as r1, r2, r3, it won't do it, because "r" & iCtr is a string, not a range. what do you think? set it as a string & then convert it to a range? i don't know how to do that but i could probably find it in the newsgroup cuz i've seen it before....... just for info, where do you find the groupname property on a control? i couldn't find it in the properties box. do you have to set it programatically? thanks a lot! susan On Mar 19, 2:13 pm, "Susan" wrote: thank you merjet, i will give it a try! looks like i was close, but i couldn't get my brain around it. i'll let you know how it turns out. susan On Mar 19, 1:44 pm, "merjet" wrote: The following works for 2 Frames with 4 Option Buttons in each. Each Option Button has it's Groupname property set to "Frame1" or "Frame2". You probably want to put the code in a different Sub. Hth, Merjet Private Sub UserForm_Click() Dim iCtr As Long Dim oControl As Control Dim ws As Worksheet Dim myRange As Range Dim rFound As Range Dim sCaption As String Set ws = Sheets("Sheet2") 'this is how i can incrementally check each frame For iCtr = 1 To 2 For Each oControl In Me.Controls If TypeName(oControl) = "OptionButton" Then If oControl.GroupName = "Frame" & iCtr Then 'now i need to see which option button = true within each frame... If oControl.Value = True Then Set myRange = ws.Range("d8:j23") sCaption = oControl.Caption 'the captions of each option button are 1, 2, 3, etc. Set rFound = myRange.Find(What:=sCaption, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If rFound Is Nothing Then MsgBox "Caption not found in range" End If 'column i contains the numeric that needs incrementing ws.Range("i" & rFound.Row) = ws.Range("i" & rFound.Row) + 1 End If End If End If Next oControl Next iCtr End Sub- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
check ocontrols within frames within userform
just for info, where do you find the groupname property on a control? i couldn't find it in the properties box. do you have to set it programatically? never mind about this one - i found it. duh. :) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
check ocontrols within frames within userform
On Mar 19, 1:29 pm, "Susan" wrote:
Set myRange = ws.Range("d8:j23") this range has to be able to increment thru each question's range using the frame's iCtr number. if i set it as a static range, then it won't move down for each question. I set it to cover questions in all frames, assuming every question unique. If that's not the case, then set myRange dynamically. You could use a Select Case structure or create Range Names on the worksheet and use the Names. Merjet |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
check ocontrols within frames within userform
On Mar 19, 3:53 pm, "merjet" wrote:
On Mar 19, 1:29 pm, "Susan" wrote: Set myRange = ws.Range("d8:j23") this range has to be able to increment thru each question's range using the frame's iCtr number. if i set it as a static range, then it won't move down for each question. I set it to cover questions in all frames, assuming every question unique. If that's not the case, then set myRange dynamically. You could use a Select Case structure or create Range Names on the worksheet and use the Names. Merjet no, all the questions are not unique. questions 1-6 have a rating scale of 5 - 1. so therefore if frame 1's answer is "5" & frame 2's answer is also "5" then it gets added 2x to #5 frame 1. i was just searching the newsgroup because i was trying a different approach: Public myRange As Range Public sRange As String sRange = "r" & iCtr 'this is a string (i.e., r2) myRange = sRange 'range = string (i.e., r2 = r2) i thought i could "trick" vba into reading the range from the string, but it's not working. (that darn vba is just too smart!). i looked into "redimming", but that seems to work with arrays. so i had just come to the same conclusion you did, that i need to do a case structure with each iCtr number setting it's individual range. i hate case structures, though, i've never been able to get one to work properly (i've always falling back on multiple if statements). but it guess the time has come to tackle it!!!. thanks very much for your help, merjet! susan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec) | Excel Discussion (Misc queries) | |||
Problem loading userform with frames | Excel Programming | |||
Multiple Frames on a userform | Excel Programming | |||
Problem with Frames in userform | Excel Programming | |||
overlaying frames on a userform | Excel Programming |