returning value from a form to a spreadsheet

Hi Todd,

You can use the .TopLeftCell property to return a range reference of the cell the top-left corner of the button is in. See attached code snippet for a basic example, please ask if you need any clarification or anything!

Matt

Private Sub CommandButton1_Click()
'To get the address
 MsgBox CommandButton1.TopLeftCell.Address(False, False)
 
'or, to get a range object
 Dim CLL As Excel.Range 'Object
 Set CLL = CommandButton1.TopLeftCell
End Sub

Select allOpen in new window

Ok i think i get this but in my case the button that I need to get the topleftcell address for is on the spreadsheet and its name is just plainly Button 5.  I have a module that i used define a sub procedure call show Weighted_Curve_Form that initializes and shows the form...This is the sub procedure that the button on my spreadsheet is linked to...Is this module where i should place the code?  

Ok, you must have used a button from the Forms toolbar (my code was for if you used a button from the Control Toolbox toolbar).

What you can do to get the address (and similarly the range object), is a format like the attached. The only difference is that you have to qualify the button with the Sheet name and the button name in the Shapes collection.

Matt

 MsgBox Sheets("Sheet1").Shapes("Button 5").TopLeftCell.Address(0, 0)
 
'or, to give you an example of returning value from a form to a spreadsheet'
 Sheets("Sheet1").Shapes("Button 5").TopLeftCell.Value = Weighted_Curve_Form.TextBox1.Text

Select allOpen in new window

Excellent that worked...Now my only question is...Do i have to make a button on my spreadsheet for every row or can i somehow make the button appear when i click in the appropriate row under the TC column...

There isn't a foolproof way of moving the button around depending on where is clicked.. but you could do something similar in a different way.

Give this a try, see what you think. Right-click the worksheet tab (at the bottom) you're using, go to "View Code", and in the code pane there, paste in the attached code snippet (the first one). Change the "Set TCColumn = ..." line to the column you're using, and now the cell itself will trigger the form to show. I wrote it so if the specific column is clicked (and only one cell is selected), the form shows, otherwise that column is cleared and the cell in the selected row (in your tc column) turns grey and says "SHOW FORM".

If you do this, you wouldn't use the .TopLeftCell property as described above, but you could just use Sheets("Sheet1").ActiveCell to refer to that cell. Do remember that I clear the TCColumn in my code in order to display "SHOW FORM". If you don't want this, and simply want the column clicking to be the trigger only, go with my second code snippet below

Sorry if I've overcomplicated things, but moving the button around to the active row isn't as easy as something like this.

Matt

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim TCColumn As Range
 Set TCColumn = Columns("D")
 If Target.Cells.Count = 1 And Target.Column = TCColumn.Column Then
  Weighted_Curve_Form.Show
 Else
  TCColumn.Clear
  With Intersect(Target.EntireRow, TCColumn)
   .Value = "SHOW FORM"
   .Interior.ColorIndex = 15
  End With
 End If
End Sub
 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim TCColumn As Range
 Set TCColumn = Columns("D")
 If Target.Cells.Count = 1 And Target.Column = TCColumn.Column Then
  Weighted_Curve_Form.Show
 End If
End Sub

Select allOpen in new window

Matt,

I looked your code over and i think this will do just what i want.  However i typed this code into the sheet code but nothing happened.  Do i have to initialize this somehow.  It just so happened that Column D was the column i needed...Your Good!!....but it didn't work...I have also named the entire column TC.  I tried that in place of D and that didn't work either....I've only been doing this for about a week so i may have missed something simple...

Hmm.... you shouldn't have to initialize it or anything, assuming your macros are enabled (which they should be if you're already using them).  I was going to verify that you put it in the worksheet object's codepane, but your wording suggests you did just that. Using   Columns("D")   would be correct; if you wanted to refer to the named range you would use     Range("TC")  
A couple thoughts..

Did you copy/paste both codes into it? If so, that could be causing the error if there are both events with the same name there

Did you try going to    Debug   ->   Compile      ? That would tell you if there are any obvious errors there (though it will compile the whole workbook and not just the module)


Assuming all of that is as it should be, try replacing your code with the attached snippet. It will simply return a message box with the new selection's address; we can verify that your events are working correctly.  Also, you could just try restarting excel and see if that helps (though I'm sure you've done this too).

Matt

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 MsgBox "You selected " & Target.Address(0, 0)
End Sub

Select allOpen in new window

Matt,

Ok I found the problem..I didn't copy/paste because i like to type it out myself so i actually go through all the code and try to pick out things that i understand....I put an Underscore between Selection and Change in the  Sub name...However now its responding but it says type mismatch.  And in the line of code " Set TCColumn = "D", the "D" is highlighted......Not sure what i did wrong..

Excellent...That worked.....I did miss the column call...Thank You so much...I've upped the points to 500...I have a hard time knowing how hard a question is that i don't know the answer to LOL!!..I think you've earned this one Thanks for all your help

You could have put the points at 20 and I wouldn't have answered any differently (and would still continue to ask follow ups) :) I like to help, and I especially like it when the person asking wants to learn. Glad to help!