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

Changing Values of a list
 

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

Changing Values of a list
 

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

Changing Values of a list
 

=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" wrote in message
...
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

Changing Values of a list
 

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" wrote in message
...
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

Changing Values of a list
 

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" wrote in message
...
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" wrote in message
...
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