Excel: Use a command Button to Link to another sheet 3

[coolcarls]

I am trying to use a command button to link to another sheet in my work book. The tricky part for me is..... I want to enter data in a row, when I get to the last cell in the row, I need a button to be there, when the button is pressed I want sheet2 to come up (like a hyper-link) and the data in the last cell that I put data in be copied into a cell on sheet2. Now when I go back to sheet1, the button needs to be cut and pasted (or to tha effect)to the row below, like a floating button, and so on!!!

 

[JGillespie]

Personally I would not use a button. I think it would be much easier to run your macro (to take you to Sheet2 and copy a value) from a keyboard shortcut. You could also put a fixed button on the toolbar for people who forget the shortcut.

Let me know if sounds like what you are after. Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[SkipVought]

Hi,
If you want a button, consider this thread for some assistance (although it is regarding a Calendar Object, the preinciple is the same for a CommanfButton Object)

I am working on another problem now, but I will be glad to add a few more suggestions later if you care to go in this direction. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

...now I have some time.

Here's what you might consider doing.

1. Add a CommandButton to your Sheet1 using the Control Toolbox. You can toggle between design mode and run mode with the Design Mode/Exit Design Mode Buttons on the Toolbar.

2. In Design Mode, doubleclick the CommandButton -- this gets you to the CommendButton_Click event code. This is where you will take the value in the last cell and write it on Sheet2.

3. In the VBE, in the Sheet1 Code window, there are 2 drop down boxes above the window, Object and Procedure. In the Object select Worksheet and in the Procedure select Worksheet_SelectionChange. Here is where you will make the CommandButton Visible, whenever you select a particular Column, for instance. Let's say, when you reach Column 5, you want the CommandButton to be Visible -- or better yet, you might want the button to appear when the Value in Column 5 changes, so the Worksheet_Change might be better. Whatever you think is better, this code will make the CommandButton Visible...

ActiveSheet.CommandButton1.Visible = TRUE

You might want the button to be adjacent to the cell that you changed, so...

With ActiveSheet.CommandButton
   .Visible = TRUE
   .Top = Target.Top
   .Left = Target.Offset(0, 1).Left 
End With

4. You will also want to make sure that the Value in the changed cell is stored. In the Object drop down select General, in Procedure select (Declarations) Declare the variable -- Dim TargetValue. and in the Worksheet_Change code add -- TargetValue = Target.Value

5. Back to the CommandButton1_Click code.

'switch to Sheet2
Sheet2.Activate
'determine the place you want to put your value and put it!
Cells(whatRow, WhatCol).Value = TargetValue
'clear out TargetValue
TargetValue = Empty
'Switch back to Sheet1
Sheet1.Activate
'make the CommandButton invisible
ActiveSheet.CommandButton1.Visible = FALSE

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Since Carl & I have been working on this off-line, here's the code that I have submitted...

Sub UpdateSheet2(TargetValue)
    'switch to Sheet2
    Sheet2.Activate
    'determine the place you want to put your value and put it!
    Cells(2, 4).Value = TargetValue
    'Switch back to Sheet1  - If you want Sheet2 to stay active, terminate sub here<<<
    Sheet1.Activate
    'make the CommandButton invisible
    ActiveSheet.CommandButton1.Visible = False
End Sub

In the Sheet1 object code ....

Dim TargetValue
Private Sub CommandButton1_Click()
    ThisWorkbook.UpdateSheet2 TargetValue
    'clear out TargetValue
    TargetValue = Empty
End Sub

 

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column <> 8 Then Exit Sub
        With ActiveSheet.CommandButton1
            .Visible = True
            .Top = Target.Top
            .Left = Target.Offset(0, 1).Left
        End With
        TargetValue = .Value
    End With
End Sub

If you left Sheet2 active, then, when Sheet1 is activated, the CommandButton should be INVISIBLE -- so...

Private Sub Worksheet_Activate()
    'make the CommandButton invisible
    ActiveSheet.CommandButton1.Visible = False

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[coolcarls]

Skip Vought has been nothing short of amazing!! There seems to be no limit to the depth of his knowledge and skill, not to mention the willingness to share with others. I'm certain that there are many of you out there who have benefitted from him too!! Thanks Skip. And thanks to every one else who has helped me.