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

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

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

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

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

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

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

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

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

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

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

Denys