Solved: link to return to form

[Emoncada]

I have this code

Private Sub CommandButton1_Click()
TxtFinalStatus = "Complete"
End Sub
Private Sub CommandButton2_Click()
'Add New Button to your UserForm so you press to Apply Data to Sheet
Dim RowNext As Integer
'last row of data puls one row
RowNext = Worksheets("Server Build Template").Cells(65536, 1).End(xlUp).Row + 1
'Cells(Row Number,Column Number)
With Worksheets("Server Build Template")
.Cells(RowNext, 1) = TxtName.Value
.Cells(RowNext, 2) = LblDate.Caption
.Cells(RowNext, 3) = TxtServerName.Value
.Cells(RowNext, 4) = TxtLocation.Value
If Me.ButtonG4 = True Then .Cells(RowNext, 5) = Me.LblML370G4.Caption
If Me.ButtonG5 = True Then .Cells(RowNext, 5) = Me.LblML370G5.Caption
.Cells(RowNext, 6) = TxtCtsContact.Value
.Cells(RowNext, 7) = TxtTracking.Value
If Me.Button72 = True Then .Cells(RowNext, 8) = Me.Lbl728GB.Caption
If Me.Button146 = True Then .Cells(RowNext, 8) = Me.Lbl146GB.Caption
.Cells(RowNext, 9) = TxtDrive1SN.Value
.Cells(RowNext, 10) = TxtDrive2SN.Value
.Cells(RowNext, 11) = TxtDrive3SN.Value
.Cells(RowNext, 12) = TxtDrive4SN.Value
.Cells(RowNext, 13) = TxtFinalStatus.Value
.Cells(RowNext, 14) = TxtSignature.Value
End With
ServerBuild.PrintForm
'You can't save a worksheet
ActiveWorkbook.Save
End Sub

Private Sub CommandButton3_Click()
ServerBuild.Hide
End Sub

Private Sub CommandButton4_Click()
TxtName.Value = ""
LblDate.Caption = ""
TxtServerName.Value = ""
TxtLocation.Value = ""
TxtCtsContact.Value = ""
TxtTracking.Value = ""
Me.ButtonG4 = False
Me.ButtonG5 = False
Me.Button146 = False
Me.Button72 = False
TxtDrive1SN.Value = ""
TxtDrive2SN.Value = ""
TxtDrive3SN.Value = ""
TxtDrive4SN.Value = ""
TxtFinalStatus.Value = ""
TxtSignature.Value = ""
TxtName.SetFocus
End Sub

Private Sub CommandButton5_Click()
TxtSignature = TxtName.Value
End Sub

Private Sub Txtname_AfterUpdate()
If TxtName = "" Then
LblDate.Caption = ""
Else
LblDate.Caption = Format(Now, "mm/dd/yyyy") & " " & Format(Now, "hh:mm:ss AM/PM")
End If
End Sub

How Can I make the TxtServerName a link on the spreadsheet so if I need to return to it I can click it and the form would pop up and pull the data of that line into the form for editing?
Any Ideas?

[Bob Phillips]

Not tested, but maybe you could use double-click on the server name column




Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "C1:C20" '<== change to suit
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)
ServerBuild.TxtCtsContact.Value = Me.Cells(.Row, 6)
ServerBuild.TxtTracking.Value = Me.Cells(.Row, 7)
ServerBuild.Lbl728GB.Caption = Me.Cells(.Row, 8)
ServerBuild.Lbl146GB.Caption = Me.Cells(.Row, 8)
ServerBuild.TxtDrive1SN.Value = Me.Cells(.Row, 9)
ServerBuild.TxtDrive2SN.Value = Me.Cells(.Row, 10)
ServerBuild.TxtDrive3SN.Value = Me.Cells(.Row, 11)
ServerBuild.TxtDrive4SN.Value = Me.Cells(.Row, 12)
ServerBuild.TxtFinalStatus.Value = Me.Cells(.Row, 13)
ServerBuild.TxtSignature.Value = Me.Cells(.Row, 14)
End With
End If
Cancel = True
ServerBuild.Show
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[Emoncada]

That's not working it gives me an error on

TxtName.Value = Me.Cells(ActiveCell.Row, 1)

[Bob Phillips]

I spotted that error and corrected it. You obviously got the code before my correction. Get the code from that previous post again.

[Emoncada]

Nice only thing is


ServerBuild.LblML370G4.Caption = Me.Cells(.Row, 5)
ServerBuild.LblML370G5.Caption = Me.Cells(.Row, 5)
And

ServerBuild.Lbl728GB.Caption = Me.Cells(.Row, 8)
ServerBuild.Lbl146GB.Caption = Me.Cells(.Row, 8)

Should be

ServerBuild.ButtonG4.Value = Me.Cells(.Row, 5)
ServerBuild.ButtonG5.Value = Me.Cells(.Row, 5)
And

ServerBuild.Button72.Value = Me.Cells(.Row, 8)
ServerBuild.Button146.Value = Me.Cells(.Row, 8)

But They are not giving me the one selected, instead it comes up with both in a light greyish color. (These are radio buttons) Can that be fixed?

[Bob Phillips]

I get it.



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "C1:C20" '<== change to suit
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
ServerBuild.TxtDrive1SN.Value = Me.Cells(.Row, 9)
ServerBuild.TxtDrive2SN.Value = Me.Cells(.Row, 10)
ServerBuild.TxtDrive3SN.Value = Me.Cells(.Row, 11)
ServerBuild.TxtDrive4SN.Value = Me.Cells(.Row, 12)
ServerBuild.TxtFinalStatus.Value = Me.Cells(.Row, 13)
ServerBuild.TxtSignature.Value = Me.Cells(.Row, 14)
End With
End If
Cancel = True
ServerBuild.Show
End Sub

[Bob Phillips]

BTW, you don't need to stick a label next to an optionbutton, it has its own caption.

[Emoncada]

Everything looks good but for one thing. Somehow the LblML370G4 & LblML370G5 change to whatever is selected in the row.
So if it's ML370 G4 in the row it changes the form to (2) ML370 G4's and the same for ML370 G5. I don't see anything different in the code that can make that happen can you?

[Emoncada]

Ok I see you have this


ServerBuild.LblML370G4.Caption = Me.Cells(.Row, 5)
ServerBuild.LblML370G5.Caption = Me.Cells(.Row, 5)
I removed it and it works great.

[Bob Phillips]

Sorry, I thought I removed all of them.

[Emoncada]

Thanks For your Help I Appreciate it.