Naming different ranges in the same column
 

Hi,

In my column H, I have three possibilities: Project, Support or Group. Is is possible with VBA to name a range of cells according to the data entered in those cells ?

In other words, I need to have three range of cells (that will change every day)

Before I only had one possibility which was Project which was assigned to a combobox in a userform. Therefore I used the following code:


Sub Proj()
On Error Resume Next
Dim L As Long
Sheet3.Select
With ActiveSheet()
If Range("B3").Value = "" Then Exit Sub

L = .Range("B65536").End(xlUp).Row
.Range("B2:B" & L).Name = "Proj"
End With
End Sub

Now, I want the combobox to reflect only the rows that contain the word Project or Support or Group in the column H If the user choose the checkbox Group, only the rows containing the word group in the column H should appear in the combobox. Same for Support or Project .

Any ideas ?

Thank you for your time
Denys

Naming different ranges in the same column
 

Hi Denys,

Am Wed, 15 Apr 2015 07:40:36 -0700 (PDT) schrieb :

In my column H, I have three possibilities: Project, Support or Group. Is is possible with VBA to name a range of cells according to the data entered in those cells ?

In other words, I need to have three range of cells (that will change every day)

sort your data by column Hand try following code:

Sub Test()
Dim strData As String
Dim varData As Variant
Dim i As Long

strData = "Project,Group,Support"
varData = Split(strData, ",")

For i = 0 To UBound(varData)
ActiveSheet.Names.Add Name:=varData(i), RefersTo:= _
"=OFFSET($H$1,MATCH(""" & varData(i) & _
""",$H$1:$H$1000,0)-1,,COUNTIF($H:$H,""" & varData(i) & """))"
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Naming different ranges in the same column
 

Hi Claus,

Thank you so much !!! Only one thing. When I try thr following:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then Combobox8.Rowsource= "Projects"
end sub

I get the message that Excel could not set the Rowsource Property. But the name appears in the name Manager..... Have I missed anything ?

Thanks again

Denys

Naming different ranges in the same column
 

Try...

If CheckBox1 = True Then Combobox8.Rowsource=Range("Projects")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Naming different ranges in the same column
 

Hi Denys,

Am Wed, 15 Apr 2015 09:53:58 -0700 (PDT) schrieb :

Private Sub CheckBox1_Click()
If CheckBox1 = True Then Combobox8.Rowsource= "Projects"
end sub

why do you need a combobox in which all entries are "Projects"?
In range("Projects") every cell has the value "Projects"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Naming different ranges in the same column
 

Hi Denys,

Am Wed, 15 Apr 2015 19:09:11 +0200 schrieb Claus Busch:

why do you need a combobox in which all entries are "Projects"?
In range("Projects") every cell has the value "Projects"

the names are sheet scope names. Rowsource has to be a string. If the
range is on Sheet1 then try:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then UserForm1 _
.ComboBox8.RowSource = "Sheet1!Project"
End Sub

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Naming different ranges in the same column
 

Hi Denys,

Am Wed, 15 Apr 2015 09:53:58 -0700 (PDT) schrieb
:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then Combobox8.Rowsource= "Projects"
end sub

why do you need a combobox in which all entries are "Projects"?
In range("Projects") every cell has the value "Projects"


Regards
Claus B.

Probably needs to be more like...

If CheckBox1 = True Then Combobox8.List = Split(strData, ",")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Naming different ranges in the same column
 

Oh! boy !!!
I am not going to win an oscar on this one !!!!!
I forgot to mention that in the combobox the list should be taken on the column B if the value in Column H is Group....or Projects This is why I needed to have the different names, so only the ones corresponding should appear in the combobox...

It is true that it did not make any sense to have a dropdown with all the same word into it !!!

So I presume theere should be an offsett somewhere...

Thank you again

Denys

Naming different ranges in the same column
 

Here's where I stand for now:

But so far, everything appears on the list....not only the range Group...

Private Sub ComboBox8_Change()

Nom = ComboBox8
Dim c
With Sheet3.Range("Group")
Set c = .Find(What:=Nom, After:=Sheet3.Range("B3"), LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
fistA = c.Address
Application.GoTo Reference:=Sheet3.Range(c.Address)
End If
Me.ComboBox8.Value = ActiveCell.Value
Me.TextBox4.Value = ActiveCell.Offset(0, 11).Value
Me.TextBox5.Value = ActiveCell.Offset(0, 12).Value
Me.TextBox6.Value = ActiveCell.Offset(0, 9).Text
Me.TextBox7.Value = ActiveCell.Offset(0, 17).Text
'Sheet1.Range("J1").Value = Me.TextBox6.Value''

End With
Sheet1.Select
End Sub

It should have captured only the last four of the list !!!!

Denys

Naming different ranges in the same column
 

Hi Denys,

Am Wed, 15 Apr 2015 10:56:09 -0700 (PDT) schrieb :

I forgot to mention that in the combobox the list should be taken on the column B if the value in Column H is Group....or Projects This is why I needed to have the different names, so only the ones corresponding should appear in the combobox...

then create also names for column B. Use as names the 3 left characters:

Sub Test()
Dim strData As String
Dim varData As Variant
Dim i As Long

strData = "Project,Group,Support"
varData = Split(strData, ",")

For i = 0 To UBound(varData)
ActiveSheet.Names.Add Name:=varData(i), RefersTo:= _
"=OFFSET($H$1,MATCH(""" & varData(i) & _
""",$H$1:$H$1000,0)-1,,COUNTIF($H:$H,""" & varData(i) & """))"
ActiveSheet.Names.Add Name:=Left(varData(i), 3), RefersTo:= _
"=Offset(" & varData(i) & ",,-6)"
Next
End Sub

Then insert the values into the combobox with:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then UserForm1 _
.ComboBox8.RowSource = "Sheet1!Pro"
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Naming different ranges in the same column
 

OK Claus.... I'll work on this and come back.... In the meantime... Thank you for your time

Denys