VBA Userform - Set Named Range as Variable from Option Buttons?

[dropkickweasel]

Hi,

I think what I'm trying to do is logical and I'm just unaware of the syntax to make it run.

I have a sheet called "Board".

On "Board" I have several named ranges.
Four of them refer to tenants ("Tenant1" etc).
Many more refer to flats in a building ("Flat23, etc).

I have a userform with option buttons to track which tenants are in which flats.
The option buttons are in two groups: Four buttons for Tenants1 - 4, (called "Tenant1" and grouped as "Tenants"), and many more for the flats (called "Flat23" and grouped as "Flats").

The Tenant named ranges contain information about each tenant.
The Flat named ranges are empty locations on the sheet into which the tenant information is to be copied/pasted.

Using the following code:

Private Sub PlaceTenantButton_Click()

If Me.Tenant1.Value Then Board.Range("Flat23").Value = Board.Range("Tenant1").Value
If Me.Tenant2.Value Then Board.Range("Flat23").Value = Board.Range("Tenant2").Value
If Me.Tenant3.Value Then Board.Range("Flat23").Value = Board.Range("Tenant3").Value
If Me.Tenant4.Value Then Board.Range("Flat23").Value = Board.Range("Tenant4").Value

End Sub

I am able to copy/paste the relevant tenant information into the location "Flat23".

What I can't work out is how to make the destination range to be a variable dependent on the Flat location selected with the second set of option buttons.

I'm fairly new to VBA so am trying to piece together what I can and have come up with this so far:

Dim Flat As Range

Set Flat = OptionButton.Flats

If Me.Flat23.Value Then Flat = Range("Flat23")
If Me.Flat24.Value Then Flat = Range("Flat24")

But I'm getting a 'Run-time error 424: Object required' error message on the second line and I don't know what the appropriate syntax should be to set the variable as the result of the option button selection.

I'm hoping that once someone is able to help me with that, then changing the range in the first part of the code to

If Me.Tenant1.Value Then Board.Range(Flat).Value = Board.Range("Tenant1").Value

will put the tenant information in the right place.

As a side note, I'm assuming it's fairly bad practice to have named ranges and option buttons called the same thing. Is it problematic or just bad practice?

 

[6StringJazzer]

Dim Flat As Range

Set Flat = OptionButton.Flats

If Me.Flat23.Value Then Flat = Range("Flat23")
If Me.Flat24.Value Then Flat = Range("Flat24")

You are trying to set a Range to...I don't know what. OptionButton.Flats looks illegal to me. Also whatever it is, it isn't a worksheet Range.

I think you want something like this. I have not tested this because of the effort needed to set up a test file from scratch.

This code loops all the controls to find which Flatxx option button is selected. When it finds it, it saves the name and uses it as a Range name later.

Dim Flat As String

Dim C As Control
For Each C In Me.Controls
   If Left(C.Name, 4) = "Flat" Then
      If C.Value Then
         Flat = C.Name
         Exit For
      End If
  End If
Next C

If Me.Tenant1.Value Then Board.Range(Flat).Value = Board.Range("Tenant1").Value

You could also use a similar strategy for the Tenant and then this last line of code (actually four lines of code) becomes

Board.Range(Flat).Value = Board.Range(Tenat).Value

Also, it is not at all a bad practice to named ranges and option buttons named the same thing. The way they are used in code syntax is completely different and so would not create any ambiguity in the code. The only danger is if if the programmer gets confused

 

[dropkickweasel]

Dim Flat As Range

Set Flat = OptionButton.Flats

If Me.Flat23.Value Then Flat = Range("Flat23")
If Me.Flat24.Value Then Flat = Range("Flat24")

You are trying to set a Range to...I don't know what. OptionButton.Flats looks illegal to me. Also whatever it is, it isn't a worksheet Range.

I think you want something like this. I have not tested this because of the effort needed to set up a test file from scratch.

This code loops all the controls to find which Flatxx option button is selected. When it finds it, it saves the name and uses it as a Range name later.

Dim Flat As String

Dim C As Control
For Each C In Me.Controls
   If Left(C.Name, 4) = "Flat" Then
      If C.Value Then
         Flat = C.Name
         Exit For
      End If
  End If
Next C

If Me.Tenant1.Value Then Board.Range(Flat).Value = Board.Range("Tenant1").Value

You could also use a similar strategy for the Tenant and then this last line of code (actually four lines of code) becomes

Board.Range(Flat).Value = Board.Range(Tenat).Value

Also, it is not at all a bad practice to named ranges and option buttons named the same thing. The way they are used in code syntax is completely different and so would not create any ambiguity in the code. The only danger is if if the programmer gets confused

That works perfectly, thank you!

Sorry to be a pest, but how would I change the second part of the code?

This is what I have and is working fine:

Private Sub PlaceTenantButton_Click()

Dim Flat As String

Dim C As Control
For Each C In Me.Controls
   If Left(C.Name, 4) = "Flat" Then
      If C.Value Then
         Flat = C.Name
         Exit For
      End If
  End If
Next C

If Me.Tenant1.Value Then Board.Range(Flat).Value = Board.Range("Tenant1").Value
If Me.Tenant2.Value Then Board.Range(Flat).Value = Board.Range("Tenant2").Value
If Me.Tenant3.Value Then Board.Range(Flat).Value = Board.Range("Tenant3").Value
If Me.Tenant4.Value Then Board.Range(Flat).Value = Board.Range("Tenant4").Value


End Sub

I tried copying the first bit that you provided with the following changes:

Private Sub PlaceTenantButton_Click()

Dim Flat As String

Dim C As Control
For Each C In Me.Controls
   If Left(C.Name, 4) = "Flat" Then
      If C.Value Then
         Flat = C.Name
         Exit For
      End If
  End If
Next C

Dim Tenant As String

Dim D As Control
For Each D In Me.Controls
   If Left(D.Name, 6) = "Tenant" Then
      If D.Value Then
         Flat = D.Name
         Exit For
      End If
  End If
Next D

Board.Range(Flat).Value = Board.Range(Tenant).Value

End Sub

but I get a 'range of object - worksheet failed' error message.

Any ideas on what I'm doing wrong? (It's not a worry either way as the existing (probably less-efficient) code is functioning fine, just curious for my own learning).

I'm glad the repetitive names don't cause any actual problems - and 'programmer' would be a very generous term for this perpetually confused hobbyist :]

 

[6StringJazzer]

You have a bug. Make the change shown in red:

Private Sub PlaceTenantButton_Click()

Dim Flat As String

Dim C As Control
For Each C In Me.Controls
   If Left(C.Name, 4) = "Flat" Then
      If C.Value Then
         Flat = C.Name
         Exit For
      End If
  End If
Next C

Dim Tenant As String

Dim D As Control
For Each D In Me.Controls
   If Left(D.Name, 6) = "Tenant" Then
      If D.Value Then
         Tenant= D.Name
         Exit For
      End If
  End If
Next D

Board.Range(Flat).Value = Board.Range(Tenant).Value

End Sub

 

[dropkickweasel]

So, more diligent proof-reading needed next time, thanks!

It now works perfectly, thank you very much.

Well, obviously now I'm having ideas on how to "improve" my interface further, so expect me to be asking more question(s) over the next few days as I try to work out what can be done (and what I can do) with userforms...