| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help sought re vbmodeless form rendering a class module useless
Hey guys,
Here is my problem: I have a form which has a button on it that when clicked, changes the form from modal to modeless - the reason for this is so people can then go to excel and copy and paste list of numbers into the form from a sheet they may have been emailed. The problem is that the form being modeless then renders class modules I have useless. The class modules are there so certain buttons and text boxes do the same things. In terms of my text boxes, the class module detects somebody has hit enter then makes the frame below it visible and ready for inputing more. This works perfectly until I make the form modeless - is there a reason for this and is there a way to kick the damn thing into working order? I tried the repaint option, thinking that a sokution to a modeless displayscreen problem i had may work but it didn't do the trick in this case. Thank you in advance for your help. Clint |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help sought re vbmodeless form rendering a class module useless
Hi Clint,
'----------------- I have a form which has a button on it that when clicked, changes the form from modal to modeless - the reason for this is so people can then go to excel and copy and paste list of numbers into the form from a sheet they may have been emailed. The problem is that the form being modeless then renders class modules I have useless. The class modules are there so certain buttons and text boxes do the same things. In terms of my text boxes, the class module detects somebody has hit enter then makes the frame below it visible and ready for inputing more. This works perfectly until I make the form modeless - is there a reason for this and is there a way to kick the damn thing into working order? I tried the repaint option, thinking that a sokution to a modeless displayscreen problem i had may work but it didn't do the trick in this case. '----------------- It would be useful if you were to post your button code. I would have anticipated a run time error. As a matter of interest, why seek to change the display mode? Why do you not simply display the userform non modally? --- Regards, Norman |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help sought re vbmodeless form rendering a class module useless
Hi Norman,
The reason I change the display mode is so as to allow excel to be accessed by the user so they can view their spreadsheet then copy values from it then they can paste it into the userform running. My code revolving around the class module that is impacted is as follows: Private Sub Workbook_open() Dim Buttons() As New Class1 Dim Boxes() As New Class2 Dim Checks() As New Class3 Dim ctl As Control ButtonCount = 0 BoxCount = 0 CheckCount = 0 For Each ctl In UserForm4.Controls If TypeName(ctl) = "TextBox" Then BoxCount = BoxCount + 1 ReDim Preserve Boxes(1 To BoxCount) Set Boxes(BoxCount).TBGroup = ctl End If If TypeName(ctl) = "CommandButton" Then If ctl.Name < "OKButton" Then 'Skip the OKButton ButtonCount = ButtonCount + 1 ReDim Preserve Buttons(1 To ButtonCount) Set Buttons(ButtonCount).ButtonGroup = ctl End If End If If TypeName(ctl) = "CheckBox" Then CheckCount = CheckCount + 1 ReDim Preserve Checks(1 To CheckCount) Set Checks(CheckCount).CBGroup = ctl End If Next ctl Call Initial_setup End Sub and the actual class module.... Public WithEvents TBGroup As MSFOrms.TextBox Private Sub TBGroup_change() Namby = Right(TBGroup.Name, Len(TBGroup.Name) - 7) If Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(32) Then UserForm4.Controls(TBGroup.Name).Value = Left(UserForm4.Controls(TBGroup.Name).Value, Len(UserForm4.Controls(TBGroup.Name).Value) - 1) If Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(10) Or Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(13) Then If Val(Namby) <= 70 Then If Int(((Val(Namby) - 1) / 3)) = ((Val(Namby) - 1) / 3) Then TempPhrase = UCase(UserForm4.Controls(TBGroup.Name).Value) For Strip = 1 To Len(TempPhrase) If Asc(Mid(TempPhrase, Strip, 1)) 41 Then Phrase = Phrase + Mid(TempPhrase, Strip, 1) Next Strip UserForm4.Controls(TBGroup.Name).Value = Phrase Phrase = "" If UserForm4.Controls("Frame" & (((Val(Namby) + 2) / 3) + 1)).Visible = False Then UserForm4.Controls("Frame" & (((Val(Namby) + 2) / 3) + 1)).Visible = True UserForm4.Controls("Label" & (25 + (((Val(Namby) + 2) / 3) + 1))).Visible = True End If UserForm4.Controls("TextBox" & (Val(Namby) + 3)).SetFocus End If End If End If End Sub What appears to happen when i change it to modeess is that the enter key does not seem to be recognised any more and where the above should stop a carriage return and strip it out of what was entered then go to a new line, it seems to do a soft enter. So in effect the event trapping for detecting chr(10) is not working any longer once it is modeless. As far as I know there is no different between non modal and modeless or are they actually different? Thanks for your help. Clint "Norman Jones" wrote in message ... Hi Clint, '----------------- I have a form which has a button on it that when clicked, changes the form from modal to modeless - the reason for this is so people can then go to excel and copy and paste list of numbers into the form from a sheet they may have been emailed. The problem is that the form being modeless then renders class modules I have useless. The class modules are there so certain buttons and text boxes do the same things. In terms of my text boxes, the class module detects somebody has hit enter then makes the frame below it visible and ready for inputing more. This works perfectly until I make the form modeless - is there a reason for this and is there a way to kick the damn thing into working order? I tried the repaint option, thinking that a sokution to a modeless displayscreen problem i had may work but it didn't do the trick in this case. '----------------- It would be useful if you were to post your button code. I would have anticipated a run time error. As a matter of interest, why seek to change the display mode? Why do you not simply display the userform non modally? --- Regards, Norman |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help sought re vbmodeless form rendering a class module useless
Hi Clint,
I still do not understand why you do not display the Userform modelessly. The code that I woululd like to have seen posted is the userform button code to change the display mode. --- Regards, Norman "The Clint" wrote in message ... Hi Norman, The reason I change the display mode is so as to allow excel to be accessed by the user so they can view their spreadsheet then copy values from it then they can paste it into the userform running. My code revolving around the class module that is impacted is as follows: Private Sub Workbook_open() Dim Buttons() As New Class1 Dim Boxes() As New Class2 Dim Checks() As New Class3 Dim ctl As Control ButtonCount = 0 BoxCount = 0 CheckCount = 0 For Each ctl In UserForm4.Controls If TypeName(ctl) = "TextBox" Then BoxCount = BoxCount + 1 ReDim Preserve Boxes(1 To BoxCount) Set Boxes(BoxCount).TBGroup = ctl End If If TypeName(ctl) = "CommandButton" Then If ctl.Name < "OKButton" Then 'Skip the OKButton ButtonCount = ButtonCount + 1 ReDim Preserve Buttons(1 To ButtonCount) Set Buttons(ButtonCount).ButtonGroup = ctl End If End If If TypeName(ctl) = "CheckBox" Then CheckCount = CheckCount + 1 ReDim Preserve Checks(1 To CheckCount) Set Checks(CheckCount).CBGroup = ctl End If Next ctl Call Initial_setup End Sub and the actual class module.... Public WithEvents TBGroup As MSFOrms.TextBox Private Sub TBGroup_change() Namby = Right(TBGroup.Name, Len(TBGroup.Name) - 7) If Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(32) Then UserForm4.Controls(TBGroup.Name).Value = Left(UserForm4.Controls(TBGroup.Name).Value, Len(UserForm4.Controls(TBGroup.Name).Value) - 1) If Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(10) Or Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(13) Then If Val(Namby) <= 70 Then If Int(((Val(Namby) - 1) / 3)) = ((Val(Namby) - 1) / 3) Then TempPhrase = UCase(UserForm4.Controls(TBGroup.Name).Value) For Strip = 1 To Len(TempPhrase) If Asc(Mid(TempPhrase, Strip, 1)) 41 Then Phrase = Phrase + Mid(TempPhrase, Strip, 1) Next Strip UserForm4.Controls(TBGroup.Name).Value = Phrase Phrase = "" If UserForm4.Controls("Frame" & (((Val(Namby) + 2) / 3) + 1)).Visible = False Then UserForm4.Controls("Frame" & (((Val(Namby) + 2) / 3) + 1)).Visible = True UserForm4.Controls("Label" & (25 + (((Val(Namby) + 2) / 3) + 1))).Visible = True End If UserForm4.Controls("TextBox" & (Val(Namby) + 3)).SetFocus End If End If End If End Sub What appears to happen when i change it to modeess is that the enter key does not seem to be recognised any more and where the above should stop a carriage return and strip it out of what was entered then go to a new line, it seems to do a soft enter. So in effect the event trapping for detecting chr(10) is not working any longer once it is modeless. As far as I know there is no different between non modal and modeless or are they actually different? Thanks for your help. Clint "Norman Jones" wrote in message ... Hi Clint, '----------------- I have a form which has a button on it that when clicked, changes the form from modal to modeless - the reason for this is so people can then go to excel and copy and paste list of numbers into the form from a sheet they may have been emailed. The problem is that the form being modeless then renders class modules I have useless. The class modules are there so certain buttons and text boxes do the same things. In terms of my text boxes, the class module detects somebody has hit enter then makes the frame below it visible and ready for inputing more. This works perfectly until I make the form modeless - is there a reason for this and is there a way to kick the damn thing into working order? I tried the repaint option, thinking that a sokution to a modeless displayscreen problem i had may work but it didn't do the trick in this case. '----------------- It would be useful if you were to post your button code. I would have anticipated a run time error. As a matter of interest, why seek to change the display mode? Why do you not simply display the userform non modally? --- Regards, Norman |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help sought re vbmodeless form rendering a class module useless
I am displaying it modelessly, which is what is causing the problem with the
class module. The code for the button is as follows: Private Sub ModeChange_Click() UserForm4.Hide UserForm4.Show vbModeless End Sub Regards, Clint "Norman Jones" wrote in message ... Hi Clint, I still do not understand why you do not display the Userform modelessly. The code that I woululd like to have seen posted is the userform button code to change the display mode. --- Regards, Norman "The Clint" wrote in message ... Hi Norman, The reason I change the display mode is so as to allow excel to be accessed by the user so they can view their spreadsheet then copy values from it then they can paste it into the userform running. My code revolving around the class module that is impacted is as follows: Private Sub Workbook_open() Dim Buttons() As New Class1 Dim Boxes() As New Class2 Dim Checks() As New Class3 Dim ctl As Control ButtonCount = 0 BoxCount = 0 CheckCount = 0 For Each ctl In UserForm4.Controls If TypeName(ctl) = "TextBox" Then BoxCount = BoxCount + 1 ReDim Preserve Boxes(1 To BoxCount) Set Boxes(BoxCount).TBGroup = ctl End If If TypeName(ctl) = "CommandButton" Then If ctl.Name < "OKButton" Then 'Skip the OKButton ButtonCount = ButtonCount + 1 ReDim Preserve Buttons(1 To ButtonCount) Set Buttons(ButtonCount).ButtonGroup = ctl End If End If If TypeName(ctl) = "CheckBox" Then CheckCount = CheckCount + 1 ReDim Preserve Checks(1 To CheckCount) Set Checks(CheckCount).CBGroup = ctl End If Next ctl Call Initial_setup End Sub and the actual class module.... Public WithEvents TBGroup As MSFOrms.TextBox Private Sub TBGroup_change() Namby = Right(TBGroup.Name, Len(TBGroup.Name) - 7) If Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(32) Then UserForm4.Controls(TBGroup.Name).Value = Left(UserForm4.Controls(TBGroup.Name).Value, Len(UserForm4.Controls(TBGroup.Name).Value) - 1) If Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(10) Or Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(13) Then If Val(Namby) <= 70 Then If Int(((Val(Namby) - 1) / 3)) = ((Val(Namby) - 1) / 3) Then TempPhrase = UCase(UserForm4.Controls(TBGroup.Name).Value) For Strip = 1 To Len(TempPhrase) If Asc(Mid(TempPhrase, Strip, 1)) 41 Then Phrase = Phrase + Mid(TempPhrase, Strip, 1) Next Strip UserForm4.Controls(TBGroup.Name).Value = Phrase Phrase = "" If UserForm4.Controls("Frame" & (((Val(Namby) + 2) / 3) + 1)).Visible = False Then UserForm4.Controls("Frame" & (((Val(Namby) + 2) / 3) + 1)).Visible = True UserForm4.Controls("Label" & (25 + (((Val(Namby) + 2) / 3) + 1))).Visible = True End If UserForm4.Controls("TextBox" & (Val(Namby) + 3)).SetFocus End If End If End If End Sub What appears to happen when i change it to modeess is that the enter key does not seem to be recognised any more and where the above should stop a carriage return and strip it out of what was entered then go to a new line, it seems to do a soft enter. So in effect the event trapping for detecting chr(10) is not working any longer once it is modeless. As far as I know there is no different between non modal and modeless or are they actually different? Thanks for your help. Clint "Norman Jones" wrote in message ... Hi Clint, '----------------- I have a form which has a button on it that when clicked, changes the form from modal to modeless - the reason for this is so people can then go to excel and copy and paste list of numbers into the form from a sheet they may have been emailed. The problem is that the form being modeless then renders class modules I have useless. The class modules are there so certain buttons and text boxes do the same things. In terms of my text boxes, the class module detects somebody has hit enter then makes the frame below it visible and ready for inputing more. This works perfectly until I make the form modeless - is there a reason for this and is there a way to kick the damn thing into working order? I tried the repaint option, thinking that a sokution to a modeless displayscreen problem i had may work but it didn't do the trick in this case. '----------------- It would be useful if you were to post your button code. I would have anticipated a run time error. As a matter of interest, why seek to change the display mode? Why do you not simply display the userform non modally? --- Regards, Norman |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help sought re vbmodeless form rendering a class module useless
Hi Clint,
I am displaying it modelessly, which is what is causing the problem with the class module. The code for the button is as follows: Private Sub ModeChange_Click() UserForm4.Hide UserForm4.Show vbModeless End Sub No, I meant display the Userform modelessly when you first call it! In other words, *never* show it modally. --- Regards, Norman "The Clint" wrote in message ... I am displaying it modelessly, which is what is causing the problem with the class module. The code for the button is as follows: Private Sub ModeChange_Click() UserForm4.Hide UserForm4.Show vbModeless End Sub Regards, Clint "Norman Jones" wrote in message ... Hi Clint, I still do not understand why you do not display the Userform modelessly. The code that I woululd like to have seen posted is the userform button code to change the display mode. --- Regards, Norman "The Clint" wrote in message ... Hi Norman, The reason I change the display mode is so as to allow excel to be accessed by the user so they can view their spreadsheet then copy values from it then they can paste it into the userform running. My code revolving around the class module that is impacted is as follows: Private Sub Workbook_open() Dim Buttons() As New Class1 Dim Boxes() As New Class2 Dim Checks() As New Class3 Dim ctl As Control ButtonCount = 0 BoxCount = 0 CheckCount = 0 For Each ctl In UserForm4.Controls If TypeName(ctl) = "TextBox" Then BoxCount = BoxCount + 1 ReDim Preserve Boxes(1 To BoxCount) Set Boxes(BoxCount).TBGroup = ctl End If If TypeName(ctl) = "CommandButton" Then If ctl.Name < "OKButton" Then 'Skip the OKButton ButtonCount = ButtonCount + 1 ReDim Preserve Buttons(1 To ButtonCount) Set Buttons(ButtonCount).ButtonGroup = ctl End If End If If TypeName(ctl) = "CheckBox" Then CheckCount = CheckCount + 1 ReDim Preserve Checks(1 To CheckCount) Set Checks(CheckCount).CBGroup = ctl End If Next ctl Call Initial_setup End Sub and the actual class module.... Public WithEvents TBGroup As MSFOrms.TextBox Private Sub TBGroup_change() Namby = Right(TBGroup.Name, Len(TBGroup.Name) - 7) If Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(32) Then UserForm4.Controls(TBGroup.Name).Value = Left(UserForm4.Controls(TBGroup.Name).Value, Len(UserForm4.Controls(TBGroup.Name).Value) - 1) If Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(10) Or Right(UserForm4.Controls(TBGroup.Name).Value, 1) = Chr(13) Then If Val(Namby) <= 70 Then If Int(((Val(Namby) - 1) / 3)) = ((Val(Namby) - 1) / 3) Then TempPhrase = UCase(UserForm4.Controls(TBGroup.Name).Value) For Strip = 1 To Len(TempPhrase) If Asc(Mid(TempPhrase, Strip, 1)) 41 Then Phrase = Phrase + Mid(TempPhrase, Strip, 1) Next Strip UserForm4.Controls(TBGroup.Name).Value = Phrase Phrase = "" If UserForm4.Controls("Frame" & (((Val(Namby) + 2) / 3) + 1)).Visible = False Then UserForm4.Controls("Frame" & (((Val(Namby) + 2) / 3) + 1)).Visible = True UserForm4.Controls("Label" & (25 + (((Val(Namby) + 2) / 3) + 1))).Visible = True End If UserForm4.Controls("TextBox" & (Val(Namby) + 3)).SetFocus End If End If End If End Sub What appears to happen when i change it to modeess is that the enter key does not seem to be recognised any more and where the above should stop a carriage return and strip it out of what was entered then go to a new line, it seems to do a soft enter. So in effect the event trapping for detecting chr(10) is not working any longer once it is modeless. As far as I know there is no different between non modal and modeless or are they actually different? Thanks for your help. Clint "Norman Jones" wrote in message ... Hi Clint, '----------------- I have a form which has a button on it that when clicked, changes the form from modal to modeless - the reason for this is so people can then go to excel and copy and paste list of numbers into the form from a sheet they may have been emailed. The problem is that the form being modeless then renders class modules I have useless. The class modules are there so certain buttons and text boxes do the same things. In terms of my text boxes, the class module detects somebody has hit enter then makes the frame below it visible and ready for inputing more. This works perfectly until I make the form modeless - is there a reason for this and is there a way to kick the damn thing into working order? I tried the repaint option, thinking that a sokution to a modeless displayscreen problem i had may work but it didn't do the trick in this case. '----------------- It would be useful if you were to post your button code. I would have anticipated a run time error. As a matter of interest, why seek to change the display mode? Why do you not simply display the userform non modally? --- Regards, Norman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CLASS MODULE & SIMPLE MODULE | Excel Discussion (Misc queries) | |||
| vbModeless user form crashes Excel | Excel Programming | |||
| Class module in VBA | Excel Programming | |||
| let and get in class module | Excel Programming | |||
| Variable from a sheet module in a class module in XL XP | Excel Programming | |||
Hybrid Mode
