Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Visibility / Selectionchange Problem
I have a range containing vlookup formuals (AX17:AX31). The index
number for these functions are contained in a separate range (BK17:BK31), and are each manipulated by a seperate spinner button. I am curious if there is a way to make it so that when a particular cell is selected only the button affecting the formula is visible. Is this even possible? If anyone can think of a better way to do this let me know. I don't know if there is a way to make a floating button that aligns itself/adjusts the formula of the selected cell. Any ideas or solutions will be greatly appreciated. Thank You. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Visibility / Selectionchange Problem
I put a spinner from the Forms control toolbox on the worksheet.
I called it "Spinner 1". Then I used this code behind the worksheet: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim mySpinner As Shape Dim myVal As Long Dim LinkedCellColumn As String LinkedCellColumn = "BX" Set mySpinner = Nothing On Error Resume Next Set mySpinner = Me.Shapes("Spinner 1") On Error GoTo 0 If mySpinner Is Nothing Then MsgBox "Design error--no spinner" Exit Sub End If If Target.Cells.Count 1 Then mySpinner.Visible = False Exit Sub End If If Intersect(Me.Range("17:31"), Target) Is Nothing Then mySpinner.Visible = False Exit Sub End If mySpinner.Visible = True With Target mySpinner.Top = .Top mySpinner.Left = .Offset(0, 1).Left myVal = Me.Cells(Target.Row, LinkedCellColumn).Value mySpinner.OLEFormat.Object.LinkedCell _ = Me.Cells(Target.Row, LinkedCellColumn).Address(external:=True) Me.Cells(Target.Row, LinkedCellColumn).Value = myVal End With End Sub When I select a cell in rows 17:31, the spinner pops up to the cell to the right in the same row--don't select IV or add some code to stop that error! mastermind wrote: I have a range containing vlookup formuals (AX17:AX31). The index number for these functions are contained in a separate range (BK17:BK31), and are each manipulated by a seperate spinner button. I am curious if there is a way to make it so that when a particular cell is selected only the button affecting the formula is visible. Is this even possible? If anyone can think of a better way to do this let me know. I don't know if there is a way to make a floating button that aligns itself/adjusts the formula of the selected cell. Any ideas or solutions will be greatly appreciated. Thank You. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Visibility / Selectionchange Problem
Hey, thanks a lot. The code works great. However, I need a little bit
of help in modifying it now. The cells containing the Vlookup formulas are merged cells (AX17:BG17....all the way down to the last row AX31:BG31), and while the code works great on a single cell it will not run when the cells are merged. Is there a way to make it respond to the merged cells? Thank you Dave Peterson wrote: I put a spinner from the Forms control toolbox on the worksheet. I called it "Spinner 1". Then I used this code behind the worksheet: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim mySpinner As Shape Dim myVal As Long Dim LinkedCellColumn As String LinkedCellColumn = "BX" Set mySpinner = Nothing On Error Resume Next Set mySpinner = Me.Shapes("Spinner 1") On Error GoTo 0 If mySpinner Is Nothing Then MsgBox "Design error--no spinner" Exit Sub End If If Target.Cells.Count 1 Then mySpinner.Visible = False Exit Sub End If If Intersect(Me.Range("17:31"), Target) Is Nothing Then mySpinner.Visible = False Exit Sub End If mySpinner.Visible = True With Target mySpinner.Top = .Top mySpinner.Left = .Offset(0, 1).Left myVal = Me.Cells(Target.Row, LinkedCellColumn).Value mySpinner.OLEFormat.Object.LinkedCell _ = Me.Cells(Target.Row, LinkedCellColumn).Address(external:=True) Me.Cells(Target.Row, LinkedCellColumn).Value = myVal End With End Sub When I select a cell in rows 17:31, the spinner pops up to the cell to the right in the same row--don't select IV or add some code to stop that error! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button Visibility / Selectionchange Problem
You mean it fails when it tries to put the spinner in the cell to the right?
If that's the case, maybe you can put it in the cell to the left (.offset(0,-1)). If you mean that it's a problem when you change selection, then maybe just dropping that check for multiple cells: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim mySpinner As Shape Dim myVal As Long Dim LinkedCellColumn As String LinkedCellColumn = "BX" Set mySpinner = Nothing On Error Resume Next Set mySpinner = Me.Shapes("Spinner 1") On Error GoTo 0 If mySpinner Is Nothing Then MsgBox "Design error--no spinner" Exit Sub End If Set Target = Target.Cells(1) If Intersect(Me.Range("17:31"), Target) Is Nothing Then mySpinner.Visible = False Exit Sub End If mySpinner.Visible = True With Target mySpinner.Top = .Top mySpinner.Left = .Offset(0, 1).Left myVal = Me.Cells(Target.Row, LinkedCellColumn).Value mySpinner.OLEFormat.Object.LinkedCell _ = Me.Cells(Target.Row, LinkedCellColumn).Address(external:=True) Me.Cells(Target.Row, LinkedCellColumn).Value = myVal End With End Sub (I hate merged cells!) mastermind wrote: Hey, thanks a lot. The code works great. However, I need a little bit of help in modifying it now. The cells containing the Vlookup formulas are merged cells (AX17:BG17....all the way down to the last row AX31:BG31), and while the code works great on a single cell it will not run when the cells are merged. Is there a way to make it respond to the merged cells? Thank you Dave Peterson wrote: I put a spinner from the Forms control toolbox on the worksheet. I called it "Spinner 1". Then I used this code behind the worksheet: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim mySpinner As Shape Dim myVal As Long Dim LinkedCellColumn As String LinkedCellColumn = "BX" Set mySpinner = Nothing On Error Resume Next Set mySpinner = Me.Shapes("Spinner 1") On Error GoTo 0 If mySpinner Is Nothing Then MsgBox "Design error--no spinner" Exit Sub End If If Target.Cells.Count 1 Then mySpinner.Visible = False Exit Sub End If If Intersect(Me.Range("17:31"), Target) Is Nothing Then mySpinner.Visible = False Exit Sub End If mySpinner.Visible = True With Target mySpinner.Top = .Top mySpinner.Left = .Offset(0, 1).Left myVal = Me.Cells(Target.Row, LinkedCellColumn).Value mySpinner.OLEFormat.Object.LinkedCell _ = Me.Cells(Target.Row, LinkedCellColumn).Address(external:=True) Me.Cells(Target.Row, LinkedCellColumn).Value = myVal End With End Sub When I select a cell in rows 17:31, the spinner pops up to the cell to the right in the same row--don't select IV or add some code to stop that error! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visibility of a command Button | Excel Programming | |||
Visibility of a command Button | Excel Programming | |||
Visibility of a command Button | Excel Programming | |||
Command Button Visibility Problem | Excel Programming | |||
selectionchange problem | Excel Programming |