Hello, I would like to change a cell source base once a selection made from a
combo box. After making a selection from the combo box the code should do
something like:

If MyComboBox = “test1” then
B1 = Sheet2!A1
Else
B1 has no data source (null)
End if

What’s the easiest way to accomplish my goal in Excel 2007?
PS. B1 is the cell in my fist Sheet.

Thank you,
Silvio

Assuming you named your Combobox MyComboBox this should work for you. Put
this code into the ComboBox_Change Event. This will fire each time someone
changes the combobox value. Hope this helps! If so, let me know, click
"YES" below.

Private Sub MyComboBox_Change()

If MyComboBox = “test1” Then
Range("B1").Formula = "=Sheet2!A1"
Else
Range("B1").Value = ""
MsgBox "B1 has no data source.", vbInformation
End If

End Sub
--
Cheers,
Ryan


"Silvio" wrote:

> Hello, I would like to change a cell source base once a selection made from a
> combo box. After making a selection from the combo box the code should do
> something like:
>
> If MyComboBox = “test1” then
> B1 = Sheet2!A1
> Else
> B1 has no data source (null)
> End if
>
> What’s the easiest way to accomplish my goal in Excel 2007?
> PS. B1 is the cell in my fist Sheet.
>
> Thank you,
> Silvio
>

Hi Ryan, I am not sure if I am missing something but regardless of my
selection from the combo boix the "else" condition always fires up even
though "test1"was selected. Any idea?



"Ryan H" wrote:

> Assuming you named your Combobox MyComboBox this should work for you. Put
> this code into the ComboBox_Change Event. This will fire each time someone
> changes the combobox value. Hope this helps! If so, let me know, click
> "YES" below.
>
> Private Sub MyComboBox_Change()
>
> If MyComboBox = “test1” Then
> Range("B1").Formula = "=Sheet2!A1"
> Else
> Range("B1").Value = ""
> MsgBox "B1 has no data source.", vbInformation
> End If
>
> End Sub
> --
> Cheers,
> Ryan
>
>
> "Silvio" wrote:
>
> > Hello, I would like to change a cell source base once a selection made from a
> > combo box. After making a selection from the combo box the code should do
> > something like:
> >
> > If MyComboBox = “test1” then
> > B1 = Sheet2!A1
> > Else
> > B1 has no data source (null)
> > End if
> >
> > What’s the easiest way to accomplish my goal in Excel 2007?
> > PS. B1 is the cell in my fist Sheet.
> >
> > Thank you,
> > Silvio
> >

Check your spelling. The Else part will only execute if the first part is
not true.


"Silvio" <(E-Mail Removed)> wrote in message
news:E631DAA8-8B78-4346-B6EC-(E-Mail Removed)...
> Hi Ryan, I am not sure if I am missing something but regardless of my
> selection from the combo boix the "else" condition always fires up even
> though "test1"was selected. Any idea?
>
>
>
> "Ryan H" wrote:
>
>> Assuming you named your Combobox MyComboBox this should work for you.
>> Put
>> this code into the ComboBox_Change Event. This will fire each time
>> someone
>> changes the combobox value. Hope this helps! If so, let me know, click
>> "YES" below.
>>
>> Private Sub MyComboBox_Change()
>>
>> If MyComboBox = "test1" Then
>> Range("B1").Formula = "=Sheet2!A1"
>> Else
>> Range("B1").Value = ""
>> MsgBox "B1 has no data source.", vbInformation
>> End If
>>
>> End Sub
>> --
>> Cheers,
>> Ryan
>>
>>
>> "Silvio" wrote:
>>
>> > Hello, I would like to change a cell source base once a selection made
>> > from a
>> > combo box. After making a selection from the combo box the code should
>> > do
>> > something like:
>> >
>> > If MyComboBox = "test1" then
>> > B1 = Sheet2!A1
>> > Else
>> > B1 has no data source (null)
>> > End if
>> >
>> > What's the easiest way to accomplish my goal in Excel 2007?
>> > PS. B1 is the cell in my fist Sheet.
>> >
>> > Thank you,
>> > Silvio
>> >

Ryan, I inserted a break to debug the code and it always tells me that
MyComboBox = Empty



"Ryan H" wrote:

> Assuming you named your Combobox MyComboBox this should work for you. Put
> this code into the ComboBox_Change Event. This will fire each time someone
> changes the combobox value. Hope this helps! If so, let me know, click
> "YES" below.
>
> Private Sub MyComboBox_Change()
>
> If MyComboBox = “test1” Then
> Range("B1").Formula = "=Sheet2!A1"
> Else
> Range("B1").Value = ""
> MsgBox "B1 has no data source.", vbInformation
> End If
>
> End Sub
> --
> Cheers,
> Ryan
>
>
> "Silvio" wrote:
>
> > Hello, I would like to change a cell source base once a selection made from a
> > combo box. After making a selection from the combo box the code should do
> > something like:
> >
> > If MyComboBox = “test1” then
> > B1 = Sheet2!A1
> > Else
> > B1 has no data source (null)
> > End if
> >
> > What’s the easiest way to accomplish my goal in Excel 2007?
> > PS. B1 is the cell in my fist Sheet.
> >
> > Thank you,
> > Silvio
> >

I did. I even changed the value to numeric (1, 2 ,3) and I am getting the
same thing. For some reason appears that the value selected from the combo
box is not been detected and the else condition always get executed.

"JLGWhiz" wrote:

> Check your spelling. The Else part will only execute if the first part is
> not true.
>
>
> "Silvio" <(E-Mail Removed)> wrote in message
> news:E631DAA8-8B78-4346-B6EC-(E-Mail Removed)...
> > Hi Ryan, I am not sure if I am missing something but regardless of my
> > selection from the combo boix the "else" condition always fires up even
> > though "test1"was selected. Any idea?
> >
> >
> >
> > "Ryan H" wrote:
> >
> >> Assuming you named your Combobox MyComboBox this should work for you.
> >> Put
> >> this code into the ComboBox_Change Event. This will fire each time
> >> someone
> >> changes the combobox value. Hope this helps! If so, let me know, click
> >> "YES" below.
> >>
> >> Private Sub MyComboBox_Change()
> >>
> >> If MyComboBox = "test1" Then
> >> Range("B1").Formula = "=Sheet2!A1"
> >> Else
> >> Range("B1").Value = ""
> >> MsgBox "B1 has no data source.", vbInformation
> >> End If
> >>
> >> End Sub
> >> --
> >> Cheers,
> >> Ryan
> >>
> >>
> >> "Silvio" wrote:
> >>
> >> > Hello, I would like to change a cell source base once a selection made
> >> > from a
> >> > combo box. After making a selection from the combo box the code should
> >> > do
> >> > something like:
> >> >
> >> > If MyComboBox = "test1" then
> >> > B1 = Sheet2!A1
> >> > Else
> >> > B1 has no data source (null)
> >> > End if
> >> >
> >> > What's the easiest way to accomplish my goal in Excel 2007?
> >> > PS. B1 is the cell in my fist Sheet.
> >> >
> >> > Thank you,
> >> > Silvio
> >> >
>
>
> .
>

Try adding .Value at the end of the Combobox in the If...Then statement.
Like this:

Private Sub MyComboBox_Change()

If MyComboBox.Value = “test1” Then
Range("B1").Formula = "=Sheet2!A1"
Else
Range("B1").Value = ""
MsgBox "B1 has no data source.", vbInformation
End If

End Sub
--
Cheers,
Ryan


"Silvio" wrote:

> Ryan, I inserted a break to debug the code and it always tells me that
> MyComboBox = Empty
>
>
>
> "Ryan H" wrote:
>
> > Assuming you named your Combobox MyComboBox this should work for you. Put
> > this code into the ComboBox_Change Event. This will fire each time someone
> > changes the combobox value. Hope this helps! If so, let me know, click
> > "YES" below.
> >
> > Private Sub MyComboBox_Change()
> >
> > If MyComboBox = “test1” Then
> > Range("B1").Formula = "=Sheet2!A1"
> > Else
> > Range("B1").Value = ""
> > MsgBox "B1 has no data source.", vbInformation
> > End If
> >
> > End Sub
> > --
> > Cheers,
> > Ryan
> >
> >
> > "Silvio" wrote:
> >
> > > Hello, I would like to change a cell source base once a selection made from a
> > > combo box. After making a selection from the combo box the code should do
> > > something like:
> > >
> > > If MyComboBox = “test1” then
> > > B1 = Sheet2!A1
> > > Else
> > > B1 has no data source (null)
> > > End if
> > >
> > > What’s the easiest way to accomplish my goal in Excel 2007?
> > > PS. B1 is the cell in my fist Sheet.
> > >
> > > Thank you,
> > > Silvio
> > >

I did and I am getting a: Run-time error '424': Object required error message

if it makes any difference I am using excel 2007 and standart combo box (not
active x control)

"Ryan H" wrote:

> Try adding .Value at the end of the Combobox in the If...Then statement.
> Like this:
>
> Private Sub MyComboBox_Change()
>
> If MyComboBox.Value = “test1” Then
> Range("B1").Formula = "=Sheet2!A1"
> Else
> Range("B1").Value = ""
> MsgBox "B1 has no data source.", vbInformation
> End If
>
> End Sub
> --
> Cheers,
> Ryan
>
>
> "Silvio" wrote:
>
> > Ryan, I inserted a break to debug the code and it always tells me that
> > MyComboBox = Empty
> >
> >
> >
> > "Ryan H" wrote:
> >
> > > Assuming you named your Combobox MyComboBox this should work for you. Put
> > > this code into the ComboBox_Change Event. This will fire each time someone
> > > changes the combobox value. Hope this helps! If so, let me know, click
> > > "YES" below.
> > >
> > > Private Sub MyComboBox_Change()
> > >
> > > If MyComboBox = “test1” Then
> > > Range("B1").Formula = "=Sheet2!A1"
> > > Else
> > > Range("B1").Value = ""
> > > MsgBox "B1 has no data source.", vbInformation
> > > End If
> > >
> > > End Sub
> > > --
> > > Cheers,
> > > Ryan
> > >
> > >
> > > "Silvio" wrote:
> > >
> > > > Hello, I would like to change a cell source base once a selection made from a
> > > > combo box. After making a selection from the combo box the code should do
> > > > something like:
> > > >
> > > > If MyComboBox = “test1” then
> > > > B1 = Sheet2!A1
> > > > Else
> > > > B1 has no data source (null)
> > > > End if
> > > >
> > > > What’s the easiest way to accomplish my goal in Excel 2007?
> > > > PS. B1 is the cell in my fist Sheet.
> > > >
> > > > Thank you,
> > > > Silvio
> > > >

Try it with this syntax:

Private Sub MyComboBox_Change()

If Me.MyComboBox.Value = "test1" Then
Range("B1").Formula = "=Sheet2!A1"
Else
ActiveSheet.Range("B1").Value = ""
MsgBox "B1 has no data source.", vbInformation
End If

End Sub





"Silvio" <(E-Mail Removed)> wrote in message
news:3BA37C3E-4D6D-47F0-8553-(E-Mail Removed)...
>I did. I even changed the value to numeric (1, 2 ,3) and I am getting the
> same thing. For some reason appears that the value selected from the combo
> box is not been detected and the else condition always get executed.
>
> "JLGWhiz" wrote:
>
>> Check your spelling. The Else part will only execute if the first part
>> is
>> not true.
>>
>>
>> "Silvio" <(E-Mail Removed)> wrote in message
>> news:E631DAA8-8B78-4346-B6EC-(E-Mail Removed)...
>> > Hi Ryan, I am not sure if I am missing something but regardless of my
>> > selection from the combo boix the "else" condition always fires up even
>> > though "test1"was selected. Any idea?
>> >
>> >
>> >
>> > "Ryan H" wrote:
>> >
>> >> Assuming you named your Combobox MyComboBox this should work for you.
>> >> Put
>> >> this code into the ComboBox_Change Event. This will fire each time
>> >> someone
>> >> changes the combobox value. Hope this helps! If so, let me know,
>> >> click
>> >> "YES" below.
>> >>
>> >> Private Sub MyComboBox_Change()
>> >>
>> >> If MyComboBox = "test1" Then
>> >> Range("B1").Formula = "=Sheet2!A1"
>> >> Else
>> >> Range("B1").Value = ""
>> >> MsgBox "B1 has no data source.", vbInformation
>> >> End If
>> >>
>> >> End Sub
>> >> --
>> >> Cheers,
>> >> Ryan
>> >>
>> >>
>> >> "Silvio" wrote:
>> >>
>> >> > Hello, I would like to change a cell source base once a selection
>> >> > made
>> >> > from a
>> >> > combo box. After making a selection from the combo box the code
>> >> > should
>> >> > do
>> >> > something like:
>> >> >
>> >> > If MyComboBox = "test1" then
>> >> > B1 = Sheet2!A1
>> >> > Else
>> >> > B1 has no data source (null)
>> >> > End if
>> >> >
>> >> > What's the easiest way to accomplish my goal in Excel 2007?
>> >> > PS. B1 is the cell in my fist Sheet.
>> >> >
>> >> > Thank you,
>> >> > Silvio
>> >> >
>>
>>
>> .
>>

Is you combobox name MyComboBox? To get the name of the combobox, click the
combobox so the dots show at the corners, then put this in the Immediate
Window in VBE:

?Selection.Name

Put that name here in this code.

NOTE: This code goes in a standard module
Sub ComboBoxChanger()

If Sheets("Sheet1").DropDowns("NAME HERE") = "test1" Then
Range("B1").Formula = "=Sheet2!A1"
Else
Range("B1").Value = ""
MsgBox "B1 has no data source.", vbInformation
End If

End Sub

Now assign this sub to the combobox.

Hope this helps! If so, let me know, click "YES" below.

--
Cheers,
Ryan


"Silvio" wrote:

> I did and I am getting a: Run-time error '424': Object required error message
>
> if it makes any difference I am using excel 2007 and standart combo box (not
> active x control)
>
> "Ryan H" wrote:
>
> > Try adding .Value at the end of the Combobox in the If...Then statement.
> > Like this:
> >
> > Private Sub MyComboBox_Change()
> >
> > If MyComboBox.Value = “test1” Then
> > Range("B1").Formula = "=Sheet2!A1"
> > Else
> > Range("B1").Value = ""
> > MsgBox "B1 has no data source.", vbInformation
> > End If
> >
> > End Sub
> > --
> > Cheers,
> > Ryan
> >
> >
> > "Silvio" wrote:
> >
> > > Ryan, I inserted a break to debug the code and it always tells me that
> > > MyComboBox = Empty
> > >
> > >
> > >
> > > "Ryan H" wrote:
> > >
> > > > Assuming you named your Combobox MyComboBox this should work for you. Put
> > > > this code into the ComboBox_Change Event. This will fire each time someone
> > > > changes the combobox value. Hope this helps! If so, let me know, click
> > > > "YES" below.
> > > >
> > > > Private Sub MyComboBox_Change()
> > > >
> > > > If MyComboBox = “test1” Then
> > > > Range("B1").Formula = "=Sheet2!A1"
> > > > Else
> > > > Range("B1").Value = ""
> > > > MsgBox "B1 has no data source.", vbInformation
> > > > End If
> > > >
> > > > End Sub
> > > > --
> > > > Cheers,
> > > > Ryan
> > > >
> > > >
> > > > "Silvio" wrote:
> > > >
> > > > > Hello, I would like to change a cell source base once a selection made from a
> > > > > combo box. After making a selection from the combo box the code should do
> > > > > something like:
> > > > >
> > > > > If MyComboBox = “test1” then
> > > > > B1 = Sheet2!A1
> > > > > Else
> > > > > B1 has no data source (null)
> > > > > End if
> > > > >
> > > > > What’s the easiest way to accomplish my goal in Excel 2007?
> > > > > PS. B1 is the cell in my fist Sheet.
> > > > >
> > > > > Thank you,
> > > > > Silvio
> > > > >