Solved

returning value from a form to a spreadsheet

Posted on 2007-12-04
11
345 Views
Last Modified: 2007-12-05
Ok i have a spreadsheet that will be used for hydrologic calculations..it will consist of One row for each watershed and multiple columns to hold values for the watershed...It one column, with a range name "TC" i have a command button that opens a form asks for info and returns an integer value.  I've read about the absolute and reletive reference but i can't quite make the connection...is there a way that the command button can pass the cell that it is overtop of into the form code? I realize the button doesn't really reside in a particular cell...  
0
Comment
Question by:ToddHawley4984
  • 6
  • 5
11 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 20403060
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

Open in new window

0
 

Author Comment

by:ToddHawley4984
ID: 20403218
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?  
0
 
LVL 35

Expert Comment

by:mvidas
ID: 20403302
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

Open in new window

0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:ToddHawley4984
ID: 20403920
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...
0
 
LVL 35

Expert Comment

by:mvidas
ID: 20404296
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

Open in new window

0
 

Author Comment

by:ToddHawley4984
ID: 20411928
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...
0
 
LVL 35

Expert Comment

by:mvidas
ID: 20412522
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

Open in new window

0
 

Author Comment

by:ToddHawley4984
ID: 20414657
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..
0
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 20414727
Ahh, I see. I've typed many things out for the same reason :) Looks now that you're just forgetting the call to the Columns() collection.

So instead of:
 Set TCColumn = "D"

Use:
 Set TCColumn = Columns("D")

If you wanted, you could add a string variable to store just the "D", though it is adding complication for little reason. I'll post an example in the code snippets section here

Matt
 Dim TCColumnLetter As String, TCColumn As Range
 TCColumnLetter = "D"  '****this would be the only line you change'
 Set TCColumn = Columns(TCColumnLetter)

Open in new window

0
 

Author Comment

by:ToddHawley4984
ID: 20415479
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


0
 
LVL 35

Expert Comment

by:mvidas
ID: 20415500
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!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now