Userform Problem

atmospheric

Well-known Member
Joined
Jul 16, 2002
Messages
565
I am trying to create a simple Order Inputting Userform and have two problems that I can't seem to get my head around:

1. I have created a button on the Userform named "Show Uninvoiced". The Invoice Number appears in Column F of the worksheet. Assuming Column A has a date (therefore an order exists in that row), but no Invoice Number, can I produce summary report showing all entries that have not been invoiced?

2. TextBox9 is YTD (Year To Date) Totals. Can I get this box to refer to the spreadsheet and sum and show all the values in Column G?

As usual, your help is greatly appreciated. :pray:
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Marc

Active Member
Joined
Feb 21, 2002
Messages
388
atmospheric said:
2. TextBox9 is YTD (Year To Date) Totals. Can I get this box to refer to the spreadsheet and sum and show all the values in Column G?

Here's one way to get the YTD.

Code:
Private Sub UserForm_Initialize()

    TextBox9.Value = Application.Sum(Range("G:G"))
    
End Sub
 

atmospheric

Well-known Member
Joined
Jul 16, 2002
Messages
565
Excellent, thanks Marc, had to do some minor tweaking to get it into my code, but that works fine.

Any offers on the first part?
 

Marc

Active Member
Joined
Feb 21, 2002
Messages
388
My idea is to move the data to another sheet and do a sort on the G column producing a list as desired. Here's the code I recorded:

Code:
Sub NoInvoiceNumber()

    Cells.Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:="="
End Sub
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723

ADVERTISEMENT

Try this (change sheet names to what works for you):

Code:
Sub listuninvoiced()
    Dim rw, c
    rw = Range("a65536").End(xlUp).Row
    For Each c In ActiveSheet.Range("f2:f" & rw).Cells
        If Len(c) < 1 Then
            Worksheets("Sheet5").Range("A" & rw & ":e" & rw).Copy _
            Destination:=Sheets("sheet8").Range("a65536").End(xlUp).Offset(1, 0)
        End If
    Next
End Sub
 

atmospheric

Well-known Member
Joined
Jul 16, 2002
Messages
565

ADVERTISEMENT

Tryingtolearn:

I've adapted your code to look like:

Private Sub BtnView_Click()
Dim rw, c
rw = Range("a65536").End(xlUp).Row
For Each c In ActiveSheet.Range("f7:f" & rw).Cells
If Len(c) < 1 Then
Worksheets("Orders").Range("A" & rw & ":e" & rw).Copy _
Destination:=Sheets("Uninvoiced").Range("a65536").End(xlUp).Offset(1, 0)
End If
Next
Sheets("Uninvoiced").Select
Unload Me
End Sub


But it's only picking up the last entry (Which IS invoiced) and copying it 3 times.

(BTW, Invoice No is in my column F)

Any suggestions as to what's wrong?
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
Sorry, was pulling the row of the final range for each "find". Corrected code is:

Code:
Private Sub BtnView_Click()
    Dim rw, c
    rw = Range("a65536").End(xlUp).Row
    For Each c In ActiveSheet.Range("f7:f" & rw).Cells
        If Len(c) < 1 Then
            Worksheets("Orders").Range("A" & c.Row & ":e" & c.Row).Copy _
            Destination:=Sheets("Uninvoiced").Range("a65536").End(xlUp).Offset(1, 0)
        End If
    Next
    Sheets("Uninvoiced").Select
    Unload Me
End Sub
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,095
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top