Changing Values of a list

My users have a form that displays specific data from a database. They have
the ability to change information in the database. Users choose a customer's
name from a list box, and the form shows the data relivant to that customer.
One item is called the lead status, and its value is either "Hot" or "Cold".

I want the user to be able to change the item's value from hot to cold, or
vise-versa, by clicking a button marked 'Change'.

I'm using the Index function to display data on the form, but that can't
(apparently) change the value of the data in the database....

Thanks in advance...
--
Don Woodman

Put a button from the Forms toolbar on that worksheet.

Assign this macro (name it what you want) to that button:

Option Explicit
Sub testme()
Dim myCell As Range
With ActiveSheet
Set myCell = .Range("a1")
With myCell
If LCase(.Value) = "cold" Then
.Value = "Hot"
Else
.Value = "Cold"
End If
End With
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


donw13 wrote:
>
> My users have a form that displays specific data from a database. They have
> the ability to change information in the database. Users choose a customer's
> name from a list box, and the form shows the data relivant to that customer.
> One item is called the lead status, and its value is either "Hot" or "Cold".
>
> I want the user to be able to change the item's value from hot to cold, or
> vise-versa, by clicking a button marked 'Change'.
>
> I'm using the Index function to display data on the form, but that can't
> (apparently) change the value of the data in the database....
>
> Thanks in advance...
> --
> Don Woodman

--

Dave Peterson

Hi Dave:
That doesn't work.

The cell that displays the lead status uses this formula:
=INDEX(Groomed!K2:K501,$E$13)
where $E$13 is the cell on the user's worksheet that holds the cell link
number, which is the row in column K, on worksheet Groomed, that contains the
current lead status, either "Hot" or "Cold".

Let's say that the value of $E$13 = 22

I need to be able to set .range("K$E$13")
So that Excel would see .range("K22")

I know what cell needs to be changed, I just can't figure out how to
communicate the row number.....

I hope this makes sense, cause I'm starting to get confused.....




--
Don Woodman


"Dave Peterson" wrote:

> Put a button from the Forms toolbar on that worksheet.
>
> Assign this macro (name it what you want) to that button:
>
> Option Explicit
> Sub testme()
> Dim myCell As Range
> With ActiveSheet
> Set myCell = .Range("a1")
> With myCell
> If LCase(.Value) = "cold" Then
> .Value = "Hot"
> Else
> .Value = "Cold"
> End If
> End With
> End With
> End Sub
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> donw13 wrote:
> >
> > My users have a form that displays specific data from a database. They have
> > the ability to change information in the database. Users choose a customer's
> > name from a list box, and the form shows the data relivant to that customer.
> > One item is called the lead status, and its value is either "Hot" or "Cold".
> >
> > I want the user to be able to change the item's value from hot to cold, or
> > vise-versa, by clicking a button marked 'Change'.
> >
> > I'm using the Index function to display data on the form, but that can't
> > (apparently) change the value of the data in the database....
> >
> > Thanks in advance...
> > --
> > Don Woodman
>
> --
>
> Dave Peterson
>

=indirect("Groomed!K" & $E$13)

what this would do is produce the value held in Groomed!K22 in the cell with
the formula. But I don't see how that wouild be different than the index
formula you already have. (except in the formula you have, it would return
the value in K23)

--
Regards,
Tom Ogilvy

"donw13" <donw13@discussions.microsoft.com> wrote in message
news:07A355AE-5F73-4662-A75C-CF5C9CF3A262@microsoft.com...
> Hi Dave:
> That doesn't work.
>
> The cell that displays the lead status uses this formula:
> =INDEX(Groomed!K2:K501,$E$13)
> where $E$13 is the cell on the user's worksheet that holds the cell link
> number, which is the row in column K, on worksheet Groomed, that contains
the
> current lead status, either "Hot" or "Cold".
>
> Let's say that the value of $E$13 = 22
>
> I need to be able to set .range("K$E$13")
> So that Excel would see .range("K22")
>
> I know what cell needs to be changed, I just can't figure out how to
> communicate the row number.....
>
> I hope this makes sense, cause I'm starting to get confused.....
>
>
>
>
> --
> Don Woodman
>
>
> "Dave Peterson" wrote:
>
> > Put a button from the Forms toolbar on that worksheet.
> >
> > Assign this macro (name it what you want) to that button:
> >
> > Option Explicit
> > Sub testme()
> > Dim myCell As Range
> > With ActiveSheet
> > Set myCell = .Range("a1")
> > With myCell
> > If LCase(.Value) = "cold" Then
> > .Value = "Hot"
> > Else
> > .Value = "Cold"
> > End If
> > End With
> > End With
> > End Sub
> >
> > If you're new to macros, you may want to read David McRitchie's intro
at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> >
> > donw13 wrote:
> > >
> > > My users have a form that displays specific data from a database.
They have
> > > the ability to change information in the database. Users choose a
customer's
> > > name from a list box, and the form shows the data relivant to that
customer.
> > > One item is called the lead status, and its value is either "Hot" or
"Cold".
> > >
> > > I want the user to be able to change the item's value from hot to
cold, or
> > > vise-versa, by clicking a button marked 'Change'.
> > >
> > > I'm using the Index function to display data on the form, but that
can't
> > > (apparently) change the value of the data in the database....
> > >
> > > Thanks in advance...
> > > --
> > > Don Woodman
> >
> > --
> >
> > Dave Peterson
> >

Hummmmm.....
How do I change the value that's in ("Groomed!K" & $E$13)?
Indirect shows me what's there, but doesn't change it...
--
Don Woodman


"Tom Ogilvy" wrote:

> =indirect("Groomed!K" & $E$13)
>
> what this would do is produce the value held in Groomed!K22 in the cell with
> the formula. But I don't see how that wouild be different than the index
> formula you already have. (except in the formula you have, it would return
> the value in K23)
>
> --
> Regards,
> Tom Ogilvy
>
> "donw13" <donw13@discussions.microsoft.com> wrote in message
> news:07A355AE-5F73-4662-A75C-CF5C9CF3A262@microsoft.com...
> > Hi Dave:
> > That doesn't work.
> >
> > The cell that displays the lead status uses this formula:
> > =INDEX(Groomed!K2:K501,$E$13)
> > where $E$13 is the cell on the user's worksheet that holds the cell link
> > number, which is the row in column K, on worksheet Groomed, that contains
> the
> > current lead status, either "Hot" or "Cold".
> >
> > Let's say that the value of $E$13 = 22
> >
> > I need to be able to set .range("K$E$13")
> > So that Excel would see .range("K22")
> >
> > I know what cell needs to be changed, I just can't figure out how to
> > communicate the row number.....
> >
> > I hope this makes sense, cause I'm starting to get confused.....
> >
> >
> >
> >
> > --
> > Don Woodman
> >
> >
> > "Dave Peterson" wrote:
> >
> > > Put a button from the Forms toolbar on that worksheet.
> > >
> > > Assign this macro (name it what you want) to that button:
> > >
> > > Option Explicit
> > > Sub testme()
> > > Dim myCell As Range
> > > With ActiveSheet
> > > Set myCell = .Range("a1")
> > > With myCell
> > > If LCase(.Value) = "cold" Then
> > > .Value = "Hot"
> > > Else
> > > .Value = "Cold"
> > > End If
> > > End With
> > > End With
> > > End Sub
> > >
> > > If you're new to macros, you may want to read David McRitchie's intro
> at:
> > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >
> > >
> > > donw13 wrote:
> > > >
> > > > My users have a form that displays specific data from a database.
> They have
> > > > the ability to change information in the database. Users choose a
> customer's
> > > > name from a list box, and the form shows the data relivant to that
> customer.
> > > > One item is called the lead status, and its value is either "Hot" or
> "Cold".
> > > >
> > > > I want the user to be able to change the item's value from hot to
> cold, or
> > > > vise-versa, by clicking a button marked 'Change'.
> > > >
> > > > I'm using the Index function to display data on the form, but that
> can't
> > > > (apparently) change the value of the data in the database....
> > > >
> > > > Thanks in advance...
> > > > --
> > > > Don Woodman
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
>
>

You can't. Formulas can only return a value to the cell in which they are
contained.

You would have to write a macro to change the value in a cell that does not
contain a formula.

--
Regards,
Tom Ogilvy

"donw13" <donw13@discussions.microsoft.com> wrote in message
news:5AD32CD5-ED1D-46B0-9ACB-110E736D571E@microsoft.com...
> Hummmmm.....
> How do I change the value that's in ("Groomed!K" & $E$13)?
> Indirect shows me what's there, but doesn't change it...
> --
> Don Woodman
>
>
> "Tom Ogilvy" wrote:
>
> > =indirect("Groomed!K" & $E$13)
> >
> > what this would do is produce the value held in Groomed!K22 in the cell
with
> > the formula. But I don't see how that wouild be different than the
index
> > formula you already have. (except in the formula you have, it would
return
> > the value in K23)
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "donw13" <donw13@discussions.microsoft.com> wrote in message
> > news:07A355AE-5F73-4662-A75C-CF5C9CF3A262@microsoft.com...
> > > Hi Dave:
> > > That doesn't work.
> > >
> > > The cell that displays the lead status uses this formula:
> > > =INDEX(Groomed!K2:K501,$E$13)
> > > where $E$13 is the cell on the user's worksheet that holds the cell
link
> > > number, which is the row in column K, on worksheet Groomed, that
contains
> > the
> > > current lead status, either "Hot" or "Cold".
> > >
> > > Let's say that the value of $E$13 = 22
> > >
> > > I need to be able to set .range("K$E$13")
> > > So that Excel would see .range("K22")
> > >
> > > I know what cell needs to be changed, I just can't figure out how to
> > > communicate the row number.....
> > >
> > > I hope this makes sense, cause I'm starting to get confused.....
> > >
> > >
> > >
> > >
> > > --
> > > Don Woodman
> > >
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Put a button from the Forms toolbar on that worksheet.
> > > >
> > > > Assign this macro (name it what you want) to that button:
> > > >
> > > > Option Explicit
> > > > Sub testme()
> > > > Dim myCell As Range
> > > > With ActiveSheet
> > > > Set myCell = .Range("a1")
> > > > With myCell
> > > > If LCase(.Value) = "cold" Then
> > > > .Value = "Hot"
> > > > Else
> > > > .Value = "Cold"
> > > > End If
> > > > End With
> > > > End With
> > > > End Sub
> > > >
> > > > If you're new to macros, you may want to read David McRitchie's
intro
> > at:
> > > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > > >
> > > >
> > > > donw13 wrote:
> > > > >
> > > > > My users have a form that displays specific data from a database.
> > They have
> > > > > the ability to change information in the database. Users choose a
> > customer's
> > > > > name from a list box, and the form shows the data relivant to that
> > customer.
> > > > > One item is called the lead status, and its value is either "Hot"
or
> > "Cold".
> > > > >
> > > > > I want the user to be able to change the item's value from hot to
> > cold, or
> > > > > vise-versa, by clicking a button marked 'Change'.
> > > > >
> > > > > I'm using the Index function to display data on the form, but that
> > can't
> > > > > (apparently) change the value of the data in the database....
> > > > >
> > > > > Thanks in advance...
> > > > > --
> > > > > Don Woodman
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> >
> >