Excel 2003 VBA problem

  • Thread starter Thread starter BJ&theBear
  • Start date Start date
B

BJ&theBear

I am currently trying to construct an idiot proof spreadsheet which is
almost complete
However the latest problem stems from the use of a userform with about
15 criteria on it to select the relevant information and copy it to a
newly created spreadsheet with just the selected criteria. This new
cpreadsheet will be used for printed reports

I have a basic knowledge of VBA but am encountering difficulties and
would appreciate some advice/help

a) The first selection Userform option button criteria is called
"optallprojects" - if this is true then I want all project IDs
selected from column 1 in the main sheet called "hoursworked"

b) The second selection option button criteria is called
"optspecificprojectno" and if this is true then I want the value in
the next box "lstselectprojectno" to become the selection criteria in
Column 1

I then want to use the values from a or b to autofilter the
spreadsheet and give me the relevant selection which can then be
copied over to a newly created spreadsheet/

I think it should be along the lines of

Private Sub createnewspreadsheet ()

'selection process only

If optallprojects.value = true
then selection.autofilter field :=1
else
selection.autofilter field:= 1 criteria1:= lstselectprojectno.value

'Once the selection is made how do you copy the filtered records to a
newly created spreadsheet when you have no idea how many records will
be part of the filter?

end sub

I am experiencing difficulties mainly because I have not done a great
deal of VBA programming in recent years. Once I have got my head
round this selection and option process then I hope I can program the
rest of the options.

Can anyone help in getting this to work

Any help or advice would be appreciated

Brian
Scotland
 
I created a small userform with two commandbuttons (ok/cancel), a listbox, and
two optionbuttons and a label.

I used optionbuttons because I want exactly one of them chosen. If I had used
checkboxes, then both could be chosen and I'd have to program against it (or
just have the program do either???).

This was the code to show the userform (in a general module):

Option Explicit
Sub testme()
UserForm1.Show
End Sub


And this was the code behind the userform:

Option Explicit
Private Sub ListBox1_Change()
Call CheckOkBTN
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()

Dim NewWks As Worksheet
Dim VisRng As Range
Dim wks As Worksheet
Dim myCrit As String

Set wks = ThisWorkbook.Worksheets("sheet1")

With wks
'remove any existing arrows!
.AutoFilterMode = False

If Me.OptionButton1.Value = True Then
.Columns(1).AutoFilter Field:=1
Else
.Columns(1).AutoFilter Field:=1, Criteria1:=Me.ListBox1.Value
End If

Set VisRng = Nothing
With .AutoFilter.Range
With .Columns(1)
If .Cells.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
MsgBox "No Data shown!"
'what should happen
Else
Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)

'this sheet exists in the current workbook
'for my testing
Set NewWks = ThisWorkbook.Worksheets("Sheet2")

VisRng.EntireRow.Copy _
Destination:=NewWks.Range("A1")

End If
End With
End With
End With

End Sub
Private Sub OptionButton1_Click()
Me.ListBox1.Visible = False
Call CheckOkBTN
End Sub
Private Sub OptionButton2_Click()
Me.ListBox1.Visible = True
Call CheckOkBTN
End Sub
Private Sub UserForm_Initialize()

With Me.ListBox1
.MultiSelect = fmMultiSelectSingle
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
End With

With Me.CommandButton1
.Caption = "Cancel"
.Enabled = True
.Cancel = True
End With

With Me.CommandButton2
.Enabled = False
.Caption = "Ok"
End With

Me.ListBox1.Visible = False

Me.Label1.Caption = "Please fill in all the choices"

End Sub
Private Sub CheckOkBTN()

Dim ctrl As Control
Dim OkToContinue As Boolean

OkToContinue = False

If Me.OptionButton1.Value = True Then
'nothing selected
OkToContinue = True
Else
If Me.OptionButton2.Value = True _
And Me.ListBox1.ListIndex >= 0 Then
'nothing chosen
OkToContinue = True
End If
End If

Me.CommandButton2.Enabled = OkToContinue

If OkToContinue = True Then
Me.Label1.Caption = ""
Else
Me.Label1.Caption = "Please fill in all the choices"
End If

End Sub
 
Back
Top