Copy Information from one sheet to another

[HansV]

It's possible, but by default, you'll be stuck because you can't exit print preview or do anything with the userform anymore.
To get around this, you must open the userform modeless. There are two ways to do this:

- Set the ShowModal property of the userform to False

or

- Use a line like this to open the userform:

UserForm1.Show False

The code for the print button can look like this:

Code: Select all

Private Sub cmdPrint_Click()
  Worksheets("donation").PrintPreview
End Sub

where cmdPrint is the name of the print button.

[adam]

I have set the ShowModal property of the form to false & tried the code but it does not seem to work.

I did modify the code as follows but still It ain't working. can you specify Where I had gone wrong.

Code: Select all

Private Sub cmdPrint_Click()
  UserForm1.Show False
  Worksheets("donation").PrintPreview
End Sub

[HansV]

Assuming that cmdPrint is a command button on UserForm1, there is no point in calling UserForm1.Show when you click the button - the form is already visible.

I would set the ShowModal property of the userform to False in the Visual Basic Editor:

x65.png

The code for the button can be as I posted in my previous reply.

[adam]

I did make the showModal False from visualbasic but i guess since the sheet was hidden it didnt not work. so I tied your second option. even that failed since the sheet was hidden. now I had modified the code as follows. in this modification the sheet first becomes visible and then shows the print preview. i want to know whether is it possible to show the print preview of the sheet directly without making the sheet first visible?

Code: Select all

Private Sub cmdPrint_Click()
Me.Hide
Worksheets("Donation").Visible = xlSheetVisible
Worksheets("Donation").PrintPreview
Worksheets("Donation").Visible = xlSheetVeryHidden
End Sub

regards
Adam

[HansV]

A sheet has to be visible before you can print preview it.

[adam]

COOL, thanks for the reply

[adam]

I want my print button on my multipage userform to do more tasks.
When I click the print button I want the hidden sheet named donation to be visible. And the following contents in the text boxes to be copied into the sheets “donation”.

txtDate to the cell C33
txtTime to the cell G33

Regards
Adam

[HansV]

You can use the following lines for that:

Code: Select all

With Worksheets("Donation")
  .Visible = xlSheetVisible
  .Range("C33") = Me.txtDate
  .Range("G33") = Me.txtTime
  ' more code if necessary
  ...
End With

[adam]

How could I modify the code so that when I press the Print button in my useform, data in the text boxes will be copied to the sheets; "Donation" simultaneously to the first empty rows of the sheets "Analysis" & "Statistics".

[HansV]

I'm not sure what you want, but you may be able to adapt the following code for your purpose:

Code: Select all

Dim r As Long
With Worksheets("Analysis")
  r = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
  .Range("A" & r) = Me.txtDate
  .Range("B" & r) = Me.txtTime
  ...
End With
With Worksheets("Statistics")
  r = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
  .Range("A" & r) = Me.txtDate
  .Range("B" & r) = Me.txtTime
  ...
End With

[adam]

Im using the following code in a userform. My problem is when i click the clear command on my form I get debug messages. can you please specify the reason for this. What is wrong in my coding?

Code: Select all

Option Explicit
Private Sub cmdCancel_Click()
Me.Hide
frmHomePage.Show
End Sub

Private Sub cmdClear_Click()
' Clear the form
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""

        End If
    Next ctl
End Sub

Private Sub cmdOK_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("StaffList")

'find  first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a Staff ID
If Trim(Me.txtStaffID.Value) = "" Then
  Me.txtStaffID.SetFocus
  MsgBox "Please enter Staff ID"
  Exit Sub
End If

    If Not IsDate(Me.txtJoinDate.Value) Then
        MsgBox "The Date box must contain a date.", vbExclamation, "Staff Registration"
        Me.txtJoinDate.SetFocus
        Exit Sub
    End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtStaffID.Value
ws.Cells(iRow, 2).Value = Me.txtDateRegistered.Value
ws.Cells(iRow, 3).Value = Me.txtNationalID.Value
ws.Cells(iRow, 4).Value = Me.txtName.Value
ws.Cells(iRow, 5).Value = Me.txtJoinDate.Value
ws.Cells(iRow, 6).Value = Me.txtDesignation.Value
ws.Cells(iRow, 7).Value = Me.txtContactNo.Value
ws.Cells(iRow, 8).Value = Me.lbxNotes.Value

'clear the data
Me.txtStaffID.Value = ""
Me.txtDateRegistered.Value = ""
Me.txtNationalID.Value = ""
Me.txtName.Value = ""
Me.txtJoinDate.Value = ""
Me.txtDesignation.Value = ""
Me.txtContactNo.Value = ""
Me.lbxNotes = ""
Me.txtStaffID.SetFocus
End Sub

Thanks in advance
Regards
Adam

[HansV]

You haven't declared the variable ctl. Insert

Dim ctl As Control

above the line

For Each ctl In Me.Controls

[adam]

Thanks for the help. the adjustment works fine.

[adam]

In my multipage form I'm having the following text boxes and labels to make up a sentence.

here is how it is.
lbltheproductof txtdonor lblis cboacceptable lblwiththatof txtrecipient lblby cbolaw lblwithsatisfactoryresults

this makes up the sentence in my form as follows;
The product of Robert Gills is acceptable with that of Sandra Walters by law with satisfactory results.

where txtdonor & txt recipient is filled from page one by Vlook up formula..

I want the whole sentence to be copied to the row 9 of my worksheet with the name "donation" when i press the PRINT command button from my form.

Can this be done in excel?

Thanks in advance.

Regards
Adam.

[HansV]

Use code like this:

Code: Select all

Dim strSentence As String
strSentence = Me.lblTheProductOf.Caption & " " & Me.txtDonor & " " & _
  Me.lblIs.Caption & " "& Me.cboAcceptable & " " & Me.lblWithThatOf.Caption & " " & _
  Me.txtRecipient & " " & Me.lblBy.Caption & " "  & Me.cboLaw & " " & _
  Me.lblWithSatisfactoryResults.Caption
Worksheets("Donation").Range("A9") = strSentence

[adam]

Thanks for the help Hans. I do really appreciate that.

By the way, I'm using the following code to put the value that I enter in the txtDonorName into the textbox txtDonor.

Code: Select all

Private Sub txtDonorName_AfterUpdate()
  Me.txtDonor = Me.txtDonorName
End Sub

What I want to know is that where should I add the above code to the following code If I want to put the value to the text box txtDonor after I enter a value in to txtDonorID in my userform

Code: Select all

Private Sub txtDonorID_AfterUpdate()
  Dim rng As Range
  If Me.txtDonorID = "" Then
    Me.txtDonorNationalID = ""
    Me.txtDonorName = ""
    Me.txtDonorAddress = ""

  Else
    Set rng = Worksheets("DonorList").Range("A9:K1048576")
    Me.txtDonorNationalID = Application.VLookup(Me.txtDonorID, rng, 3, False)
    Me.txtDonorName = Application.VLookup(Me.txtDonorID, rng, 4, False)
    Me.txtDonorAddress = Application.VLookup(Me.txtDonorID, rng, 5, False)
  End If
End Sub

I hope I have made my question clear.

Thanks in advance

Regards
Adam

[HansV]

You can add the line

Me.txtDonor = Me.txtDonorName

immediately above the End Sub.

[adam]

Thanks Hans. your modification works the way I want.
I'm using the following lines under the button Print

Code: Select all

Private Sub cmdPrint_Click()
Me.Hide
With Worksheets("DonationReport")
  .Visible = xlSheetVisible
  .Range("G36") = Me.txtTime
  .Range("C10") = Me.txtDonorID
  .Range("C25") = Me.txtRecipientID
  .Range("C35") = Me.cboStaffName
  .Range("C37") = Me.txtExpiryDate
End With
Dim r As Long
With Worksheets("Inve")
  r = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
  .Range("B" & r) = Me.txtTime
  .Range("D" & r) = Me.txtDonorID
  .Range("E" & r) = Me.txtRecipientID
  .Range("F" & r) = Me.cboStaffName

End With
With Worksheets("DonationResult")
  r = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
  .Range("B" & r) = Me.txtTime
  .Range("D" & r) = Me.txtDonorID
  .Range("E" & r) = Me.txtRecipientID
  .Range("F" & r) = Me.cboStaffName
  .Range("G" & r) = Me.cboResult
  .Range("J" & r) = Me.cboMethod
  .Range("M" & r) = Me.txtExpiryDate
End With
End Sub

I just wanted to know that where should I place the following to the above code in order to function. Should the following code be added just above the end sub line?

Code: Select all

Dim strSentence As String
strSentence = Me.lblTheProductOf.Caption & " " & Me.txtDonor & " " & _
  Me.lblIs.Caption & " "& Me.cboAcceptable & " " & Me.lblWithThatOf.Caption & " " & _
  Me.txtRecipient & " " & Me.lblBy.Caption & " "  & Me.cboLaw & " " & _
  Me.lblWithSatisfactoryResults.Caption
Worksheets("Donation").Range("A9") = strSentence

I would be pleased if you could hep me with this.

Thanks in advance.

Regards
Adam
Dim strSentence As String
strSentence = Me.lblTheProductOf.Caption & " " & Me.txtDonor & " " & _
Me.lblIs.Caption & " "& Me.cboAcceptable & " " & Me.lblWithThatOf.Caption & " " & _
Me.txtRecipient & " " & Me.lblBy.Caption & " " & Me.cboLaw & " " & _
Me.lblWithSatisfactoryResults.Caption
Worksheets("Donation").Range("A9") = strSentence[/code]

[HansV]

Hi Adam,

It doesn't really matter much where you insert the extra code - it should work whether you placed it at the beginning, in the middle or at the end.

[adam]

Thanks Hans.
It works fine.
Actually I had done a mistake when I copied the code. But Now I'm able to solve that.
Anyways, I'm happy now.

Regards
Adam