Using a command button to update a cell value

E

earlyd

Well-known Member

Joined

Dec 10, 2002

Messages

1,199

• Jan 12, 2004

• #2

First, name the merged cells - each one gets a range name.
Then create a VBA macro with the statement Range(name2).value = Range(name1).value + 1.
Create your button, right-click on it and assign the macro.

 

Upvote 0

A

Adrian1

Board Regular

Joined

Dec 30, 2003

Messages

126

• Jan 12, 2004

• #3

Okay, my cell range b6:d6 is now named index1
my cell range b7:d7 is now named index2

I have created a command button which has the following code

Private Sub CommandButton5_Click()
Range("index2").Value = Range("index1").Value + 1
End Sub

Why does this not work? I keep getting a runtime error

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Jan 12, 2004

• #4

If you're using Excel 97, altough I don't know if it would affect in this case, try setting the 'SetFocusOnClick' property of the button to False.

 

Upvote 0

E

earlyd

Well-known Member

Joined

Dec 10, 2002

Messages

1,199

• Jan 12, 2004

• #5

This worked fine for me. Check to be sure your names are there. Press F5 on your sheet and see if they appear.

 

Upvote 0

E

earlyd

Well-known Member

Joined

Dec 10, 2002

Messages

1,199

• Jan 12, 2004

• #6

Check to be sure the value in index1 is numeric

 

Upvote 0

A

Adrian1

Board Regular

Joined

Dec 30, 2003

Messages

126

• Jan 12, 2004

• #7

I have tried all of your suggestions but still it wont work. It must be something and nothing that is causing the problem.

Private Sub CommandButton5_Click()

Range("index2").Value = Range("index1").Value + 1

End Sub

Why would this give me a type mismatch error? Now I am really scratching my head.

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Jan 12, 2004

• #8

Put this in the inmediate window in VBE, and copy the results here:

?IsNumeric(Range("index1").Value), IsNumeric(Range("index2").Value), Range("index1").Value, Range("index2").Value

 

Upvote 0

A

Adrian1

Board Regular

Joined

Dec 30, 2003

Messages

126

• Jan 12, 2004

• #9

Juan,

Sorry, I am not sure what you mean there. Sorry, I know it is frustrating. Instead of naming the cell range, I applied it to single cell references.....

Range("d9").Value = Range("d10").Value + 1

...this works fine. I just seem to have problems when I apply the same to named cell ranges. What could it be. I didn't understand the last thing you asked me to do...sorry

 

Upvote 0

E

earlyd

Well-known Member

Joined

Dec 10, 2002

Messages

1,199

• Jan 12, 2004

• #10

Check to be sure that the name you added references the cell or merged cell correctly. Sounds like you may have not clicked on your cell when adding the name and so the macro is referencing a blank cell????

 

Upvote 0