Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpinButton Change Selection
Hi,
I would like to create a SpinButton with which I change from one cell to the next one (up and down). I tried this: Private Sub SpinButton1_Change() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select Cells(.Value, 1) = .Value End With End Sub The problem is that the "Cells(.Value, 1).Select" doesn't work properly. But the values in the cells are like expected "Cells(.Value, 1) = .Value". If you use the code above you can see what I mean. Many thanks for help. Best regards, Marlon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpinButton Change Selection
Marllon,
Unless I misunderstand what you are trying to do, it looks OK to me in that it puts 1 in A1, 2 in A2 , .. 10 in A10 with the Spinbutton on a Userform.. And it does this in either direction. Equally, if I change the column, the value is put in the required cell. [MS office 2003] "Marlon" wrote: Hi, I would like to create a SpinButton with which I change from one cell to the next one (up and down). I tried this: Private Sub SpinButton1_Change() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select Cells(.Value, 1) = .Value End With End Sub The problem is that the "Cells(.Value, 1).Select" doesn't work properly. But the values in the cells are like expected "Cells(.Value, 1) = .Value". If you use the code above you can see what I mean. Many thanks for help. Best regards, Marlon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpinButton Change Selection
Hi Toppers,
Sorry, I forgot to mention that I'm using a Spinbutton from the Toolbox. The Spinbutton is directly located on a Worksheet and not on a UserForm. The Values are correct - I know. But in fact I don't need them. It was just a test. I would like the cursor to switch to the next cell. If the Spinbutton is arranged on a Worksheet the behavior is very strange. Best regards, Marlon -----Original Message----- Marllon, Unless I misunderstand what you are trying to do, it looks OK to me in that it puts 1 in A1, 2 in A2 , .. 10 in A10 with the Spinbutton on a Userform.. And it does this in either direction. Equally, if I change the column, the value is put in the required cell. [MS office 2003] "Marlon" wrote: Hi, I would like to create a SpinButton with which I change from one cell to the next one (up and down). I tried this: Private Sub SpinButton1_Change() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select Cells(.Value, 1) = .Value End With End Sub The problem is that the "Cells(.Value, 1).Select" doesn't work properly. But the values in the cells are like expected "Cells(.Value, 1) = .Value". If you use the code above you can see what I mean. Many thanks for help. Best regards, Marlon . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpinButton Change Selection
Marlon,
The following works .. with B1 as Cell Link. Sub Spinner1_change() Cells(Range("b1"), 1).Select Cells(Range("b1"), 1) = Range("b1") End Sub "Marlon" wrote: Hi, I would like to create a SpinButton with which I change from one cell to the next one (up and down). I tried this: Private Sub SpinButton1_Change() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select Cells(.Value, 1) = .Value End With End Sub The problem is that the "Cells(.Value, 1).Select" doesn't work properly. But the values in the cells are like expected "Cells(.Value, 1) = .Value". If you use the code above you can see what I mean. Many thanks for help. Best regards, Marlon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpinButton Change Selection
Hi Toppers,
that doesn't help me either. Meanwhile I tried something this: Private Sub SpinButton1_SpinDown() ActiveCell.Offset(1, 0).Activate End Sub Private Sub SpinButton1_SpinUp() ActiveCell.Offset(-1, 0).Activate End Sub But it has the same effect as the first code. When I press one of the buttons of the Spinbutton the Cursor changes to the next cell (that's OK). But if I press the button again the Cursor/Selection disappears. If I press the button again the Cursor reappears in the next cell and so on. Best regards, Marlon -----Original Message----- Marlon, The following works .. with B1 as Cell Link. Sub Spinner1_change() Cells(Range("b1"), 1).Select Cells(Range("b1"), 1) = Range("b1") End Sub "Marlon" wrote: Hi, I would like to create a SpinButton with which I change from one cell to the next one (up and down). I tried this: Private Sub SpinButton1_Change() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select Cells(.Value, 1) = .Value End With End Sub The problem is that the "Cells(.Value, 1).Select" doesn't work properly. But the values in the cells are like expected "Cells(.Value, 1) = .Value". If you use the code above you can see what I mean. Many thanks for help. Best regards, Marlon . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpinButton Change Selection
Hi Toppers,
the event "GotFocus" makes nearly what I want: Private Sub SpinButton1_GotFocus() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select End With End Sub But if I press the Down-Button the Cursors goes upwards and if I press the Up-Button the Cursor goes down. Strange. - Any idea why? Best regards, Marlon -----Original Message----- Hi Toppers, that doesn't help me either. Meanwhile I tried something this: Private Sub SpinButton1_SpinDown() ActiveCell.Offset(1, 0).Activate End Sub Private Sub SpinButton1_SpinUp() ActiveCell.Offset(-1, 0).Activate End Sub But it has the same effect as the first code. When I press one of the buttons of the Spinbutton the Cursor changes to the next cell (that's OK). But if I press the button again the Cursor/Selection disappears. If I press the button again the Cursor reappears in the next cell and so on. Best regards, Marlon -----Original Message----- Marlon, The following works .. with B1 as Cell Link. Sub Spinner1_change() Cells(Range("b1"), 1).Select Cells(Range("b1"), 1) = Range("b1") End Sub "Marlon" wrote: Hi, I would like to create a SpinButton with which I change from one cell to the next one (up and down). I tried this: Private Sub SpinButton1_Change() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select Cells(.Value, 1) = .Value End With End Sub The problem is that the "Cells(.Value, 1).Select" doesn't work properly. But the values in the cells are like expected "Cells(.Value, 1) = .Value". If you use the code above you can see what I mean. Many thanks for help. Best regards, Marlon . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpinButton Change Selection
Marlon,
The difficulty you are observing is due to the SpinButton control taking focus from the worksheet (you'll notice black rectangles appear around the SpinButton arrows). The key step is forcing focus back onto the worksheet to make the cursor reappear. FYI, the CommandButton control has a property named "TakeFocusOnClick" which can be set to False to deal with this type of problem. Unfortunately, the SpinButton control doesn't have this property, so you have to kludge it with the ActiveCell.Activate command in the GotFocus event of the SpinButton. Troy Private Sub SpinButton1_GotFocus() 'Remove the focus from the SpinButton. ActiveCell.Activate End Sub Private Sub SpinButton1_SpinDown() If ActiveCell.Row < ActiveSheet.Rows.Count Then ActiveCell.Offset(1, 0).Activate End If End Sub Private Sub SpinButton1_SpinUp() If ActiveCell.Row 1 Then ActiveCell.Offset(-1, 0).Activate End If End Sub "Marlon" wrote in message ... Hi Toppers, the event "GotFocus" makes nearly what I want: Private Sub SpinButton1_GotFocus() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select End With End Sub But if I press the Down-Button the Cursors goes upwards and if I press the Up-Button the Cursor goes down. Strange. - Any idea why? Best regards, Marlon |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpinButton Change Selection
Hi Troy,
many thanks. Your explanation helps a lot to understand the whole story. It works properly for me now. Best regards, Marlon -----Original Message----- Marlon, The difficulty you are observing is due to the SpinButton control taking focus from the worksheet (you'll notice black rectangles appear around the SpinButton arrows). The key step is forcing focus back onto the worksheet to make the cursor reappear. FYI, the CommandButton control has a property named "TakeFocusOnClick" which can be set to False to deal with this type of problem. Unfortunately, the SpinButton control doesn't have this property, so you have to kludge it with the ActiveCell.Activate command in the GotFocus event of the SpinButton. Troy Private Sub SpinButton1_GotFocus() 'Remove the focus from the SpinButton. ActiveCell.Activate End Sub Private Sub SpinButton1_SpinDown() If ActiveCell.Row < ActiveSheet.Rows.Count Then ActiveCell.Offset(1, 0).Activate End If End Sub Private Sub SpinButton1_SpinUp() If ActiveCell.Row 1 Then ActiveCell.Offset(-1, 0).Activate End If End Sub "Marlon" wrote in message ... Hi Toppers, the event "GotFocus" makes nearly what I want: Private Sub SpinButton1_GotFocus() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select End With End Sub But if I press the Down-Button the Cursors goes upwards and if I press the Up-Button the Cursor goes down. Strange. - Any idea why? Best regards, Marlon . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpinButton Change Selection
Thanks for the reply. Cheers.
Troy "Marlon" wrote in message ... Hi Troy, many thanks. Your explanation helps a lot to understand the whole story. It works properly for me now. Best regards, Marlon -----Original Message----- Marlon, The difficulty you are observing is due to the SpinButton control taking focus from the worksheet (you'll notice black rectangles appear around the SpinButton arrows). The key step is forcing focus back onto the worksheet to make the cursor reappear. FYI, the CommandButton control has a property named "TakeFocusOnClick" which can be set to False to deal with this type of problem. Unfortunately, the SpinButton control doesn't have this property, so you have to kludge it with the ActiveCell.Activate command in the GotFocus event of the SpinButton. Troy Private Sub SpinButton1_GotFocus() 'Remove the focus from the SpinButton. ActiveCell.Activate End Sub Private Sub SpinButton1_SpinDown() If ActiveCell.Row < ActiveSheet.Rows.Count Then ActiveCell.Offset(1, 0).Activate End If End Sub Private Sub SpinButton1_SpinUp() If ActiveCell.Row 1 Then ActiveCell.Offset(-1, 0).Activate End If End Sub "Marlon" wrote in message ... Hi Toppers, the event "GotFocus" makes nearly what I want: Private Sub SpinButton1_GotFocus() With SpinButton1 .Min = 1 .Max = 10 Cells(.Value, 1).Select End With End Sub But if I press the Down-Button the Cursors goes upwards and if I press the Up-Button the Cursor goes down. Strange. - Any idea why? Best regards, Marlon . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spinbutton | New Users to Excel | |||
Spinbutton | Excel Discussion (Misc queries) | |||
Change row selection | Excel Worksheet Functions | |||
spinbutton? | Excel Programming | |||
Using Spinbutton to change a date | Excel Programming |