UserForms ShowModal

hatman

Well-known Member

Joined

Apr 8, 2005

Messages

2,664

• Sep 21, 2007

• #2

I think you can solve this by using a Type 8 InputBox:

Code:

    Dim Rnge As Range
    
    On Error Resume Next
    
    Set Rnge = Application.InputBox(prompt:="Select a Range", Type:=8)
    
    On Error GoTo 0
    
    If Rnge Is Nothing Then Exit Sub

 

Upvote 0

G

Giordano Bruno

Well-known Member

Joined

Jan 7, 2007

Messages

1,350

• Sep 21, 2007

• #3

Thanks hatman,

That's an interesting suggestion, but in fact the UserForm takes three separate entries. That's the reason for using a UserForm rather than an InputBox.

As a workaround, I thought of storing a "yes" or "no" in a hidden sheet and stopping or continuing the code based on the status of this cell. It's a bit clunky, but it's all I can think of at the moment.

It's hard to end the code at the showing of the UserForm as there are a number of tests which must be carried out before execution is approved.

Thanks again for you interest.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Sep 21, 2007

• #4

Why not use a RefEdit control?

 

Upvote 0

hatman

Well-known Member

Joined

Apr 8, 2005

Messages

2,664

• Sep 21, 2007

• #5

Why not use a RefEdit control?

Click to expand...

Sweet! Another control in my toolbox that I didn't know existed... and one that exhibits cool FEATURES.

 

Upvote 0

G

Giordano Bruno

Well-known Member

Joined

Jan 7, 2007

Messages

1,350

• Sep 22, 2007

• #6

Hi Norrie,

Thanks for the thought. In fact, I had earlier considered using RefEdit for some functions. It's rather buggy, but the main problem I had was that I wanted the user to select only rows. RefEdit allows the user to select rows and columns and this would be rather confusing when only the rows were acted upon.

I've never found a way of using RefEdit to allow the user to select entire rows only.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Sep 22, 2007

• #7

I don't think there is a way to restrict what the user selects.

But I can't see why you can't use what the user has selected and 'convert' it in the code to only work with entire rows.

 

Upvote 0

G

Giordano Bruno

Well-known Member

Joined

Jan 7, 2007

Messages

1,350

• Sep 23, 2007

• #8

Hi Norrie.

Thanks for your comments. Yes, I could convert it, but I don't want the confusion in the mind of the user. The other problem is that I often want three rows to be selected, a range and a target. It is more convenient for the user to do this in one operation.

 

Upvote 0