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

dropkickweasel

Board Regular
Joined
Feb 2, 2014
Messages
69
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:

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:

Code:
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
Code:
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,384
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
VBA Code:
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.

VBA Code:
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
VBA Code:
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 :)
 
Solution

dropkickweasel

Board Regular
Joined
Feb 2, 2014
Messages
69
VBA Code:
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.

VBA Code:
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
VBA Code:
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:
Code:
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:
Code:
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

Well-known Member
Joined
Jan 27, 2010
Messages
2,384
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
You have a bug. Make the change shown in red:

Rich (BB code):
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

Board Regular
Joined
Feb 2, 2014
Messages
69
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...
 

Forum statistics

Threads
1,176,079
Messages
5,901,274
Members
434,884
Latest member
ares0027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top