?
Solved

returning value from a form to a spreadsheet

Posted on 2007-12-04
11
Medium Priority
?
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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