PDA

View Full Version : Unusual request for Userform



ameritecc
03-01-2008, 12:41 PM
I will try to simplify this. Please let me know if more information is needed.
I have an extensive Excell Program which within it are 12 worksheets used for quantity takeoff of different types of material. Each worksheet has 200 rows that can be populated through various Forms. My problem lies in the fact that one form in particular called "Detail" is specific to each row and manipulates how the material listed in that row is treated and fabricated versus something identical to it in another row. I have all this solved except for one thing. My form open tab is positioned over a cell in each row so that the user will not confuse himself with which row he is identifying specific handling of a material item. If he wants to designate specific information such as painting etc for this piece of material on this row he simply clicks on the Detail tab and the form opens for his data input. All these form tabs and the form it opens in each row are identical in every respect.
At this point I am looking at creating 2400 forms to accomidate each row on 12 worksheets (200 x 12). This simply isn't acceptable in my book. Is there a way in VBA to use the one form I created, code it in such a way that once the tab for the "open form macro" is clicked on, for it to automatically define itself as to which row it is on? I feel I am missing something simple , but maybe not.

Bob Phillips
03-01-2008, 12:58 PM
See http://www.cpearson.com/Excel/FormPosition.htm

ameritecc
03-01-2008, 01:19 PM
I am sorry, I think I was misleading in what I was looking for. The form itself is quite large when opened, and is already centered on the woksheet, which is fine. It is the Macro button that opens the form and that I have positioned already in cells of each row, that I am trying to make it aware of which row it is designated for. The form itself is nothing more than several text boxes that when filled in directs the information to the correct tabulation cell on that particular row. This tabulation cell, but is directly related to the material entry on that row. When I click on the Form Macro Button and the form opens, I want to be able to fill in the text boxes and the information know which row of cells it is destined for based on the assocated row macro button I pressed. If there is a way to upload a jpg picture of this row setup and the form itself please let me know, because it could make things clearer.

mikerickson
03-01-2008, 01:45 PM
This will return the address of the TopLeft cell of the button that calls the macro. Once identified, the address or the row could be passed to the UF.

This macro will error if called from the VBEditor, it must be called by a button. (Tested on Forms Button, ActiveX not tested)
Sub IdentifyButtonLocation()
MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
End Sub

ameritecc
03-01-2008, 08:08 PM
Thanks for replying. I am not sure if I understand how to apply it. I am not understanding the topleft cell of the button reference. My button for calling the macro has no cells. There is the cell beneath it in the excell worksheet itself, but still not for sure what you mean. The button covers one cell only. I have tried your code in both excell form button, and control form buttons. Is the code you provided to be incorporated into the macro for the button? I am new at this, and have accomplished alot of hard items, but sometimes the simple ones go right past me. Please elaborate.

mikerickson
03-01-2008, 09:07 PM
The Application.Caller returns the name of the button that calls a routine. It sounded like you wanted your routine to be able to identify which button called it. And the location on the sheet of that button.

ameritecc
03-02-2008, 06:19 AM
I have played with this and incorporated your message box address into my macro on the button as below. The form comes up and the message box is behind it with the correct address of the button location cell. From there I do not know how to take that address and tie it back to the form. Plus I have to close out both the form and messagebox seperately, rather than just close the form. Could you provide some samples of code that might show me how I can pull that address into the form so that I can manipulate it from there. You have been a big help and this address identifier method for the button did work. I did try several different approaches but just can't get it.

Option Explicit
Public Sub ShopFabricationTask_Open()
'
' ShopFabricationTask_Open Macro
' Macro recorded 12/14/2003 by Dennis
'
ShopFabricationTask.Show
MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
'
End Sub

Bob Phillips
03-02-2008, 06:47 AM
I think that this is the sort of thing that you want



currRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

With UserForm1

.TextBox1.Text = ActiveSheet.Cells(currRow, "A").Value
.TextBox2.Text = ActiveSheet.Cells(currRow, "B").Value
'etc.
End With

ameritecc
03-04-2008, 07:09 PM
I get an error when I use the reference you supplied below. It does not recognize currRow on my machine. I am using Excell 2003, but that shoudn't be a problem as long as I am above version 2000. Is there something else that goes with this. Is this a variable that should be defined? I am going to post two more items relatd to this and I have to do it in a new post because this forum isn't allowing attachments in replies. I want to clarify all this with the form itself and the worksheet tab that opens the form. I will explain in the new post. I am simply stumped on this one.

currRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

Bob Phillips
03-05-2008, 01:37 AM
Yes, currRow is a variable and should be defined.