UserForm with InputBox Method Type 8 Function

[austinandrei]

Hi,
I've been trying to search for ways on how a userform can function as an inputbox of type:=8 but still got no luck.
Basically, what I want is to let the user choose a workbook and a worksheet that is why I use the type:=8 of InputBox method but I want to replicate it using a userform because inputbox lacks ways for formatting labels, boxes, etc unlike userform. Is there a way or I'll just end up using the inputbox? Thanks!

 

[Norie]

Have you looked at the RefEdit control?

 

[austinandrei]

Now I just tried using RefEdit but I can't go to any workbooks except for the activeworkbook. This is also my dilemma in my inputbox now where I can't select a cell in another workbook.

 

[My Aswer Is This]

Open your Userform like this:
UserForm1.Show Modeless

 

[austinandrei]

Yes I had that but I am not really sure where the problem lies.
I had this code on Module1:

Sub test()
UserForm1.Show vbModeless
End Sub

Then I have a userform named Userform1 with Commandbutton as Commandbutton1 then a RefEdit control as RefEdit1.
Then inside my userform code module, I have below:

Private Sub CommandButton1_Click()
 Dim rRange As Range
 Dim strAddr As String
          strAddr = RefEdit1.Value
          Set rRange = Range(strAddr)
           With rRange
                .Interior.ColorIndex = 3
                .Font.Bold = True
                .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
           End With
Unload Me
End Sub

Now, I was able to go to other workbooks and worksheets but the problem is when the RefEdit1 control is populated, the commandbutton is not functioning and my excel is no longer working.

 

[austinandrei]

I saw one code to try but I seem to not have this working as method or data member not found highlighting the "cmbWbName"
I am not sure what to edit to make it work on me:

Private Sub UserForm_Initialize()
    Dim wb As Workbook
    
    For Each wb In Application.Workbooks
        Me.cmbWbName.AddItem wb.Name
    Next
    Me.cmbWbName = ActiveWorkbook.Name
End Sub
Private Sub cmbWbName_Change()
    Dim sName As String
    
    sName = Me.cmbWbName
    If sName <> "" Then Application.Workbooks(sName).Activate
    Me.txtFullRef = ""
    Me.RefEdit1 = ""
End Sub
Private Sub RefEdit1_Change()
    If RefEdit1.Value <> "" Then
        Me.txtFullRef = "[" & Me.cmbWbName & "]" & Me.RefEdit1
    Else
        Me.txtFullRef = ""
    End If
End Sub

 

[austinandrei]

Is there no way this could function to have the RefEdit formula pickup range from another workbook.