Creating a Class ?

[The_Vulcan]

Ok...
I have forms, I want them to call a module to get some info that is returned to the form...
But I am not sure how to go about this in VBA.


### USERFORM1 ###
Private Sub UserForm_Initialize()
Call Module1.GetValues
End Sub

Private Sub CommandButton1_Click()
ActiveCell.Value = something.value
End Sub



### MODULE1 ###
Sub GetValues()
something.value = ActiveCell.Value
End Sub


Any help much appreciated.

EDIT:
I should make it clear that there are a number of forms that all need to call on the same module.
So...
### MODULE1 ###
Sub GetValues()
UserForm1.something.Value = ActiveCell.Value
End Sub

wont help.

[Leith Ross]

Hello The_Vulcan,

A couple of questions. What UserForm data will be sent to and/or returned from the Module? How will the data be processed by the Module (what do you want the Module to do?).

Thanks,
Leith Ross

[The_Vulcan]

Hi Leith

I am currently running this from the forms....


#### gather ####
Customer.Value = Range("B" & thisrow & "")
PONumber.Value = Range("C" & thisrow & "")
Division.Value = Range("D" & thisrow & "")

If Len(ActiveCell.Value) > 0 Then
arr = Split(ActiveCell.Value, ",")
For I = LBound(arr) To UBound(arr)
For j = 0 To WorkshopResources.ListCount - 1
If WorkshopResources.List(j) = arr(I) Then WorkshopResources.Selected(j) = True
Next j
Next I
End If


#### submit ####
Range("B" & thisrow & "") = Customer.Value
Range("C" & thisrow & "") = PONumber.Value
Range("D" & thisrow & "") = Division.Value

If Not (holder = "") Then
ActiveCell.Value = Left(holder, Len(holder) - 1)
End If



Thats basically it.... except that there is 20 or so values...
I currently have that repeated within a number of forms.
So there is a lot of duplication which is a waste, and if a column is added the code has to be changed on multiple forms.


"What UserForm data will be sent to and/or returned from the Module?"
= Names and Numbers
"How will the data be processed by the Module (what do you want the Module to do?)."
= Just gather cell data so that it can be passed to the forms textboxes, listboxes, calanders.
= Submit the forms data back to the cells once the forms values have been updated.

[Leith Ross]

Hello The_Vulcan,

You can place all your code into a Standard VBA Module to create a shared Macro. The trick is identifying the UserForm that called the Macro. This really is pretty simple. UserForms like Windows are part of a collection. With Windows, the active Window is always the first Window of the collection. The same is true with UserForms.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[The_Vulcan]

Hey thanks for that....

I hate it when it is something so simple that holds you up.

Much appreciated.

[The_Vulcan]

Back again, I hit a snag and can't see why.

I open my form, it calls "CurrentJobs" module below.
First it gathers the info for the job on the "Current Job" Sheet.

At this point my Form was working. And so I added the next step.

It takes the Job Number obtained from the "Current Job" Sheet and does a search on the "W'Shop Jan~Jun" sheet for the Job Number.

This works, and it finds the right row.

I then used the same bit of code as before to send the info from the "W'Shop Jan~Jun" sheet to my form. However I get this error.

Runtime error 438
Object Doesn't support this property or method.

Below in Blue is the code that works, and in Red the code that causes the error.






Sub CurrentJobs()

With UserForms(0)

'############################################# CURRENT JOBS

ActiveWorkbook.Sheets("Current Jobs").Select

thisrow = ActiveCell.Row
Range("B" & thisrow & "").Select

.Controls("Customer").Value = Range("B" & thisrow & "")
.Controls("PONumber").Value = Range("C" & thisrow & "")
.Controls("Division").Value = Range("D" & thisrow & "")
.Controls("JobNumber").Value = Range("E" & thisrow & "")
.Controls("QuoteNumber").Value = Range("F" & thisrow & "")
.Controls("Priority").Value = Range("G" & thisrow & "")
.Controls("DatePOReceived").Value = Range("H" & thisrow & "")
.Controls("CompletionDate").Value = Range("I" & thisrow & "")
.Controls("Engineer").Value = Range("J" & thisrow & "")
.Controls("JobDetails").Value = Range("K" & thisrow & "")
.Controls("Qty").Value = Range("L" & thisrow & "")
.Controls("Contact").Value = Range("M" & thisrow & "")
'.Controls("SalesOrder").Value = Range("N" & thisrow & "")
'.Controls("WShopReqForm").Value = Range("O" & thisrow & "")
.Controls("SpecialRequirements").Value = Range("P" & thisrow & "")
.Controls("FinalDestination").Value = Range("Q" & thisrow & "")
'.Controls("Email").Value = Range("R" & thisrow & "")
'.Controls("ConNotes").Value = Range("S" & thisrow & "")
'.Controls("DeliveryDocket").Value = Range("T" & thisrow & "")
'.Controls("CheckInvoice").Value = Range("U" & thisrow & "")
'.Controls("GoodsReceivedDate").Value = Range("V" & thisrow & "")
'.Controls("GoodsReceivedContact").Value = Range("W" & thisrow & "")

Range("B" & thisrow & "").Select

'############################################# WORKSHOP JAN~JUN

ActiveWorkbook.Sheets("W'Shop Jan~Jun").Activate

Dim FindString As String
Dim Rng As Range


If Not (.Controls("JobNumber") = "") Then
FindString = .Controls("JobNumber").Value

MsgBox " I AM HERE "

If Trim(FindString) <> "" Then
With ActiveSheet.Range("D8:D207") <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Problem
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
thisrow = Rng.Row
Range("B" & thisrow & "").Select

' Cells(thisrow, 1).EntireRow.Select

'.Controls("Customer").Value = Range("B" & thisrow & "")
'.Controls("PONumber").Value = Range("C" & thisrow & "")
'.Controls("JobNumber").Value = Range("D" & thisrow & "")
'.Controls("Priority").Value = Range("E" & thisrow & "")
'.Controls("WorkShopType").Value = Range("F" & thisrow & "")
'.Controls("EquipmentType").Value = Range("G" & thisrow & "")
'.Controls("Qty").Value = Range("H" & thisrow & "")
'.Controls("WRQ").Value = Range("I" & thisrow & "")
'.Controls("SerialNumber").Value = Range("J" & thisrow & "")
'.Controls("TagNumber").Value = Range("K" & thisrow & "")

MsgBox "B" & thisrow & " i WORK TO HERE"

.Controls("Comments").Value = Range("L" & thisrow & "")

Range("M" & thisrow & "").Select

'If Len(ActiveCell.Value) > 0 Then
' arr = Split(ActiveCell.Value, ",")
' For I = LBound(arr) To UBound(arr)
' For j = 0 To WorkshopResources.ListCount - 1
' If WorkshopResources.List(j) = arr(I) Then WorkshopResources.Selected(j) = True
' Next j
' Next I
'End If

' .Controls("StartDate").Value = Range("N" & thisrow & "")
'.Controls("FinishDate").Value = Range("O" & thisrow & "")

Range("B" & thisrow & "").Select



End If
End With
End If
End If




ActiveWorkbook.Sheets("Current Jobs").Select


End With
End Sub



EDIT: I think the "with" command from the search is what causes the problem... as marked...