PDA

View Full Version : Spreadsheet to Form Help



Emoncada
05-21-2007, 09:27 AM
I have this code
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C:C"
Application.EnableEvents = False

On Error GoTo ws_exit
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.AddComment.Text Text:="Double-Click Cell to View Form With this Server Information!" & Chr(10)
With Target.Comment.Shape.TextFrame.Characters.Font
.Name = "Garamond"
.Bold = True
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

'-----------------------------------------------------------------

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Const WS_RANGE As String = "C1:C200" '<== change to suit

Cancel = True
GetData Target
ServerBuild.Show

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
ServerBuild.TxtName.Value = Me.Cells(.Row, 1)
ServerBuild.LblDate.Caption = Me.Cells(.Row, 2)
ServerBuild.TxtServerName.Value = Me.Cells(.Row, 3)
ServerBuild.TxtLocation.Value = Me.Cells(.Row, 4)

'ServerBuild.LblML370G4.Caption = Me.Cells(.Row, 5)
'ServerBuild.LblML370G5.Caption = Me.Cells(.Row, 5)
If Me.Cells(.Row, 5) = ServerBuild.LblML370G4.Caption Then
ServerBuild.ButtonG4.Value = True
Else
ServerBuild.ButtonG5.Value = True
End If

ServerBuild.TxtCtsContact.Value = Me.Cells(.Row, 6)
ServerBuild.TxtTracking.Value = Me.Cells(.Row, 7)

If Me.Cells(.Row, 8).Value = ServerBuild.Lbl728GB.Caption Then
ServerBuild.Button72.Value = True
Else
ServerBuild.Button146.Value = True
End If

'etc..



I need something similar to work for my attached spreadsheet.
The difference with these two forms is this code works for a single line in the spreadsheet, with all the needed data in that one line.
The attached spreadsheet can have multiple lines from one form. Some how I need a way to be able to click somewhere and bring back the form with the data that was entered for that order. Also this code doesn't really just allow double click in column C i can basically click in any cell and it will bring the form up can i somehow make it just one column?

I know this is probably a lot, but any help would be great. Thanks

Emoncada
05-22-2007, 06:11 AM
I think the only way this might work is if I use the same code and add another spreadsheet and have the data sent to that spreadsheet also but have all the data in 1 row. But that means it will go down about 90 columns maybe more. Anybody have any input?

Emoncada
05-23-2007, 09:18 AM
BUMP!

royUK
05-23-2007, 10:19 AM
Cross posted here (http://www.ozgrid.com/forum/showthread.php?p=360372&posted=1#post360372)

Emoncada
05-24-2007, 10:03 AM
Is it somehow possible to reverse the vb code it has that sends it to the spreadsheet but have it take it back?