martinargimon
Member
HI there,
I have a problem when my nested loop starts, as the command button ( which triggers the sub-routine) starts flickering. It actually starts flickering once the 'nested For Next loop starts" ( see below).
The Subroutine is design for assigning/ selecting parcels of land ( graves) in a private cemetery. when the client buys a parcel/grave the routine assigns the parcel that the client bought by firstly selecting the order number ( from the "Orders" Table ), and then retrieves the corresponding product details from the "Products" table, and only then goes to the Sheet "Site_plan" where the routine ask the user to select the Sector and Parcel that he wants to assign the Parcel to. ( BY the way each parcel in the Site_Plan is represented as a cell ).
Lastly the Program goes to the "Beneficiaries_Burials" table where the Sector and Parcel number is entered automatically.
Hope my explanation is clear ?
Kindly let me know if you can assist?
Many Thanks
>>> use code - tags <<<
I have a problem when my nested loop starts, as the command button ( which triggers the sub-routine) starts flickering. It actually starts flickering once the 'nested For Next loop starts" ( see below).
The Subroutine is design for assigning/ selecting parcels of land ( graves) in a private cemetery. when the client buys a parcel/grave the routine assigns the parcel that the client bought by firstly selecting the order number ( from the "Orders" Table ), and then retrieves the corresponding product details from the "Products" table, and only then goes to the Sheet "Site_plan" where the routine ask the user to select the Sector and Parcel that he wants to assign the Parcel to. ( BY the way each parcel in the Site_Plan is represented as a cell ).
Lastly the Program goes to the "Beneficiaries_Burials" table where the Sector and Parcel number is entered automatically.
Hope my explanation is clear ?
Kindly let me know if you can assist?
Many Thanks
>>> use code - tags <<<
Code:
Private Sub CmdButtAssignBene_Click()
Dim OrderNo As Range
Dim SectorLot As Range
Dim ProductType As Range
Dim TotalLevelsperParcel As Integer
Dim ParcelsTotal As Integer
Dim ParcelLoopcounter As Integer
Dim TotalLevelLoopCounter As Integer
Dim ParcelAddress As Range
Dim LevelNumber As Integer
Dim SearchRangeA As Range
Dim SearchRangeB As Range
'**** Selects an Order Number from the Orders Table ******
Worksheets("Orders").Activate
Set OrderNo = Application.InputBox(Prompt:="Select the Order Number", Type:=8)
OrderNo.Select
Set ProductType = ActiveCell.Offset(0, 8)
ParcelsTotal = ActiveCell.Offset(0, 11)
'**** Selects a Product type from the Products Table ****
Worksheets("Products").Activate
Set SearchRangeA = Worksheets("Products").Range("B8:B33").Find(what:=ProductType, LookIn:=xlValues, lookat:=xlWhole)
SearchRangeA.Select
TotalLevelsperParcel = SearchRangeA.Offset(0, 4).Value
'**** The routine activates the "Site_Plan" sheet and selects a Sector where the Parcel number will be selected ****
Worksheets("SitePlan").Activate
Set SectorLot = Application.InputBox(Prompt:="Select SECTOR & LOT", Type:=8)
'**** At this stage the subroutine enters into a loop to select one or more Parcels in the sector ****
'****The variable "ParcelsTotal" determines if the Product comprises 1 , 2 or 3 Parcels ****
For ParcelLoopcounter = 1 To ParcelsTotal
Worksheets("SitePlan").Activate
Set ParcelAddress = Application.InputBox(Prompt:="Select PARCEL", Type:=8)
ParcelAddress.Value = OrderNo.Value
Application.ScreenUpdating = False
Worksheets("Beneficiaries_Burials").Activate
'**** This nested loop determines how many levels are there in the Parcel ****
For TotalLevelLoopCounter = 1 To TotalLevelsperParcel
LevelNumber = LevelNumber + 1
Range("B8").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = OrderNo.Value
ActiveCell.Offset(0, 8).Value = ProductType
ActiveCell.Offset(0, 9).Value = SectorLot
ActiveCell.Offset(0, 10).Value = ParcelAddress.Address(False, False)
ActiveCell.Offset(0, 11).Value = LevelNumber
Next TotalLevelLoopCounter
Application.ScreenUpdating = True
Next ParcelLoopcounter
'**** The subroutine enters the value of the Sector in the "Orders" table only at this stage **** "
Worksheets("Orders").Activate
Set SearchRangeB = Worksheets("Orders").Range("B8:B20").Find(what:=OrderNo, LookIn:=xlValues, lookat:=xlWhole)
SearchRangeB.Select
ActiveCell.Offset(0, 10).Value = SectorLot
'**** Finally, the subroutine returns to the table "Beneficiaries_Burials" to show the finished and populated rows of data ****
Worksheets("Beneficiaries_Burials").Activate
End Sub
Last edited by a moderator:
