Solved

returning value from a form to a spreadsheet

Posted on 2007-12-04
11
342 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ToddHawley4984
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ToddHawley4984
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

728 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

8 Experts available now in Live!

Get 1:1 Help Now