PDA

View Full Version : RefEdit issue



peteeide
03-31-2009, 04:28 PM
Hi, first time poster here.

I have one UserForm that calls another.
The first basically gets a few values then calls one of several others:



Private Sub EnterButton_Click()
...
Select Case ComponentCombo.Text
...
Case "Sub Population"
Unload XmlFormComp
XmlFormSP.Show (Modal)
...
End Select
End Sub


In XmlFormSP I have a RefEdit box. I also had a TextBox and had some code for it in App_SheetSelectionChange but pulled the TextBox and everything for it out to help troubleshoot. Also ShowModal = True in the XmlFormSP properties for the RefEdit.

Basically when I bring up XmlFormSP I can click on the RefEdit, the form collapses, I can select my range, click on the side button with the red arrow, am brought back to the form, then it's stuck. I can select a different range but I can't hit the submit button on the form or even close Excel without going throught the Task Manager.

I can worry about handling the data once I can actually push buttons on the form.

What am I missing please?

Thanks

Kenneth Hobs
03-31-2009, 05:35 PM
Welcome to the forum!

How does that not error on the Modal? Just do:
XmlFormSP.Show

peteeide
03-31-2009, 09:21 PM
Thanks Ken, that works. However when I have a TextBox in the same form and I want the user to be able to click on a cell to input the address with something like:


Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If (XmlFormSP.Visible) Then
If (XmlFormSP.spName.Tag) Then
XmlFormSP.spName.Text = ActiveCell.Address
End If
End If
End Sub

Can I not do this without XmlFormSP.Show (Modal)?

Can you not mix RefEdit with App_SheetSelectionChange/TextBox in the same form?

Thanks for your help.

peteeide
03-31-2009, 09:24 PM
Worst case I can just use RefEdits exclusively with no TextBoxes and test to make sure it's only a cell and not a range where appropriate. But also curious about using RefEdit and App_SheetSelectionChange/TextBox on the same form.

Thanks again

Kenneth Hobs
04-01-2009, 07:44 AM
I think the answer would be that sheet events are not triggered using the RefEdit control.

I am confused as to what you are trying to achieve. Can you explain it in words? You can attach an xls if you need us to troubleshoot a bit more. Make a simple one to isolate the problem.

For 1 refedit control, 3 textbox controls, this illustrates how to use the control a bit.

Private Sub RefEdit1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim r As Range
Dim shtName As String, a

If RefEdit1.Value = vbNullString Then Exit Sub

Set r = Range(RefEdit1.Value)
TextBox1.Value = r.Address
TextBox2.Value = r.Cells.Count

a = Split(RefEdit1.Value, "!")
If InStr(RefEdit1.Value, "!") > 0 Then
shtName = a(0)
Else: shtName = ActiveSheet.Name
End If

TextBox3.Value = shtName
End Sub