Form VBA

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I have sheet1 with 1000 rows, 40 columns. Is there a way
to double click either the RecID in the row (or any part
of the row) and have either a form or another sheet pop
up that shows all the data for that row in column
format? Oh and also have the capability to print that
one record from a command button. Kinda like a linked
form in Access.

Thanks in advance
Chris
 
Chris said:
I have sheet1 with 1000 rows, 40 columns. Is there a way
to double click either the RecID in the row (or any part
of the row) and have either a form or another sheet pop
up that shows all the data for that row in column
format? Oh and also have the capability to print that
one record from a command button. Kinda like a linked
form in Access.
Click to expand...

Hi, this solution uses another Worksheet. Modify it as you need it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim ActualSheet As Worksheet
Dim NewSheet As Worksheet

Set ActualSheet = ActiveSheet
Set NewSheet = Sheets.Add
ActualSheet.Select
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 40)).Copy
NewSheet.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Cancel = True
End Sub

Regards,
 
Beto said:
Hi, this solution uses another Worksheet. Modify it as you need it.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim ActualSheet As Worksheet
[ rest of code erased]
Click to expand...

I forgot to tell you this Code has to be placed in the worksheet code
module of the sheet with the data.

Regards,
 
Back
Top