refedits in a userform control class

[Doug Glancy]

Hello,

I'm working on a userform with some RefEdit controls. I found, I believe,
that there's no way to have an accelerator key for a RefEdit, so I thought
I'd create one by creating a refedit control class and defining a KeyDown
event. But now in my class module, I find that neither of the following is
a valid statement:

Public WithEvents formcontrol As RefEdit - it generates a compile error of
"Expected user-defined type, not Project."

Public WithEvents formcontrol As MSForms.RefEdit - MSForms doesn't contain a
RefEdit member

I know that RefEdits are problematic, and it's certainly being reinforced
here, but does anybody have a way to do this? I'm also just interested in
why it says "Not Project" in the compile error message above.

Thanks,

Doug

 

[Rob Bovey]

Hi Doug,

The RefEdit control is contained in its own type library which also
happens to be called RefEdit. Therefore you have to declare a reference to
it like this:

Public WithEvents mobjRefEdit As RefEdit.RefEdit

With only one "RefEdit" VBA thinks you're refering to the type library
rather than the control class.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Doug Glancy]

Thanks a lot Rob. Now I'm having trouble adding to the class and I'm
mindlessly changing things...

Here's my FormControlClass code:

Option Explicit
Public WithEvents formcontrol As RefEdit.RefEdit

Private Sub formcontrol_KeyDown(KeyCode As Integer, ByVal Shift As Integer)
Call tester
End Sub

Here's my userform code:

Option Explicit
Public formcontrols As New Collection

Private Sub UserForm_Initialize()
Call add_to_formcontrol_class
End Sub

Sub add_to_formcontrol_class()
Dim ctl As MSForms.Control
Dim refedit_control As RefEdit.RefEdit

For Each ctl In GoalSeekwithValueRangeForm.Controls 'add the option
buttons to the OptButton class
If TypeOf ctl Is RefEdit.RefEdit Then
Set refedit_control = New FormControlClass
Set refedit_control.formcontrol = ctl 'Intellisense doesn't
suggest ".formcontrol" I was just hoping
formcontrols.Add refedit_control
End If
Next
End Sub

Thanks,

Doug

 

[Doug Glancy]

Forgot to say, I get a "type mismatch" error on this line:

Set refedit_control = New FormControlClass

Thanks,

Doug

 

[Rob Bovey]

Hi Doug,

The reason you're getting a type mismatch error is because your
refedit_control variable is declared as RefEdit.RefEdit and you're trying to
set it to refer to an instance of your FormControlClass. Change the
declaration to:

Dim refedit_control As FormControlClass

and it should work as expected.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Doug Glancy]

Rob,

Like I said, I was changing things mindlessly. Thanks, that did the trick.

Doug

 

[Robert Bruce]

I'm working on a userform with some RefEdit controls. I found, I
believe, that there's no way to have an accelerator key for a
RefEdit, so I thought I'd create one by creating a refedit control
class and defining a KeyDown event.

Sorry to butt in so late, but you appear to be heading in the wrong
direction.

To define an accelerator for a refedit (or textbox or other control with no
caption), place a label on the form next to the refedit, give it a caption
describing the refedit ("Data Range", for example), set the label's
accelerator and then ensure that the label is directly before the refedit in
the form's tab order. Because a label without an associated event can't take
focus, the next control in the tab order takes it when the accelerator is
activated.

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

 

[Harald Staff]

Doh ! Of course. Very good Rob!

Best wishes Harald

 

[Jim Cone]

Rob,

Me too and thanks.

Jim Cone
San Francisco, USA

I'm working on a userform with some RefEdit controls. I found, I
believe, that there's no way to have an accelerator key for a
RefEdit, so I thought I'd create one by creating a refedit control
class and defining a KeyDown event.

Sorry to butt in so late, but you appear to be heading in the wrong
direction.

To define an accelerator for a refedit (or textbox or other control with no
caption), place a label on the form next to the refedit, give it a caption
describing the refedit ("Data Range", for example), set the label's
accelerator and then ensure that the label is directly before the refedit in
the form's tab order. Because a label without an associated event can't take
focus, the next control in the tab order takes it when the accelerator is
activated.

 

[Doug Glancy]

Robert,

Thanks. As often happens, I was half-way there. I set the labels with
accelerators, just as you described, partly because that was the way to show
the underlined keys. I even tried it to see if you would behave as you
said. Only thing is... I didn't fix the tab order. Now that I have it
works perfectly, and I can delete a couple of hours worth of code.

Ah well, it's a cheap hobby!

Thanks again,

Doug

 

[Robert Bruce]

I can delete a couple of
hours worth of code.

Ouch. I know how you feel. I spent an hour this afternoon fighting with the
VB6 IDE. Toolbarbuttons and menuitems kept disappearing seemingly at random.
Eventually I discovered that the registry had become corrupted.
HKEY_CURRENT_USER\Software\Microsoft\Visual Basic\6.0\UI did not appear in
Regedit, but when I tried to create it I got a message teling me that it
already existed. I backed up the whole of
HKEY_CURRENT_USER\Software\Microsoft\Visual Basic\6.0, deleted the UI
section of the resulting text file, deleted the branch in regedit, then
re-merged the backup. Seems to be working now. What a waste of time.

--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.