Need help with creating a dynamic excel document if a button...

[TonyU]

Hello all,

If someone could post sample code and or a reference link to read I would greatly appreciate it....

Here's what I'm trying to do.

I have one report with many fields as shown below: (this is only a sample)

What I'm trying to do is add a button to the report above that when pressed it would allow the user to select an account number so a report with two fields could be printed

Here's a sample report to print.

The first report above is updated everyday 3-5 times per hour and the report from it do not have to be printed but 4-5 times per day on SPECIFIC Account Numbers only.

Thank you very much for your time all and hope it makes sense...


P.S. If anyone has any better suggestions as to how to accomplish this task, I'm all ears.
​

 

[TonyU]

I just found out that the second report (the bottom one) is already created as part of the workbook. I just need to transfer the information in a few cells to it if a button is pressed. Thanks

 

[Zathras]

Here is one way...
Put this code in a code module:
[blue]

Option Explicit

Public SelectedRow As Long
Const COL_ACCOUNT = 1
Const COL_DESTINATION = 4
Const SHEET_DATA = "Sheet1"
Const SHEET_REPORT = "Sheet2"

Sub PrepareReport()
  Worksheets(SHEET_REPORT).Activate
  [C5] = Worksheets(SHEET_DATA).Cells(SelectedRow, COL_ACCOUNT)
  [D5] = Worksheets(SHEET_DATA).Cells(SelectedRow, COL_DESTINATION)
End Sub

[/color]
Put this code in Sheet1 code page:
[blue]

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
  If Target.Cells.Count = 1 Then
    SelectedRow = Target.Row
    PrepareReport
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  SelectedRow = Target.Row
End Sub

[/color]
Assign the "PrepareReport" macro to your button.
Adjust the Const declarations as necessary for your situation.
Adjust the "C5" and "D5" references in the macro for where you want the results. (Even better would be to assign range names.)
Select a cell from a row on the data sheet, and either double-click the cell or click your button to see the results.

 

[TonyU]

Hello Zathra, Thanks for your response. I will try it and get back to you as soon as I can.

<VBAignorance> Will come back for more advice</VBAignorance>

 

[TonyU]

Hello Zathra, I've implemented your suggestion and when I press the button the document switches to the report's page but nothing happens

You think I could email you the .xls to see it?

 

[Zathras]

It works for me in both Excel 97 and Excel 2K. Some versions seem to have difficulty with the square bracket notation. Try replacing:

  [C5] = Worksheets(SHEET_DATA).Cells(SelectedRow, COL_ACCOUNT)
  [D5] = Worksheets(SHEET_DATA).Cells(SelectedRow,

with

  Cells(5, 3) = Worksheets(SHEET_DATA).Cells(SelectedRow, COL_ACCOUNT)
  Cells(5, 4) = Worksheets(SHEET_DATA).Cells(SelectedRow, COL_DESTINATION)

Or if your data aren't in columns &quot;A&quot; and &quot;D&quot; as implied by the test data in your post, then adjust the Const declarations to the correct column numbers.

 

[TonyU]

I'm doing something wrong