Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,570
- Office Version
- 365
- 2016
- Platform
- Windows
Please, I hope someone is able to help me out of a bind. Let me try to explain the process:
A userform opens to to real the 1st ("frm_s1") of 8 frames (each frame referred to as a "service") each containing a series of controls for the user to enter pieces of data-. Only frame 1 is visible, the other 7 are not. Each frame also has 3 bottons [-], which triggers code to delete the cuurent service, [END] which triggers code to process and submit the current service and end the entry of further services, and finally [+] whic when triggered processes and submits the cuurent service and then reveals the next service frame in the sequence. I am having problems with the code associated to the [+] function. A master worksheet remains visible behinbd the form and is updated respective of the buttons selected as part of the processing and submission.
The user form opens, service frame 1 is revealed, the user enters the data and presses [+]
to continue adding services by revealing the next service range in the sequence (#2 "frm_s2").
The problem resides in this code (trnsvc_add), specifically the section highlighted in orange.
The issue:
After the press of the [+] button, worksheet core_data updates as coded.
The code in purple properly executes and provides the desired updates to that worksheet
The code in orange does NOT populate/format as coded IN RUNTIME! If I step through this code using F8, it works. Weird!
The next service frame (#2) is revealed allowing the user to enter data for that service.
It weirds out again here ... again, only in run time. Not with stepping through.
Pressing the [+] in service 2's frame will update core_data properly, the code in purple (respective of servic 2 data)... BUT ... now the update to worksheet master for service 1's orange code appears! (this should have been revealed as part of pressing [+] in service 1's frame. The orange code for service 2 does not update ws_master with service 2 data.
Here is the code for service 2's [+] button, mind you the update described above occurs before this code is encountered. (ie it updates then stops at the stop line)
My project is several 100s of lines of code and utilizes data from several independent and closed workbooks to work. For that, it isn't possible for me to provide the file to experiment with unfortunately. I hope its a simple error as it has put my project in a hold. I hope I've provided appropriate and sufficient information. If not, please ask.
A userform opens to to real the 1st ("frm_s1") of 8 frames (each frame referred to as a "service") each containing a series of controls for the user to enter pieces of data-. Only frame 1 is visible, the other 7 are not. Each frame also has 3 bottons [-], which triggers code to delete the cuurent service, [END] which triggers code to process and submit the current service and end the entry of further services, and finally [+] whic when triggered processes and submits the cuurent service and then reveals the next service frame in the sequence. I am having problems with the code associated to the [+] function. A master worksheet remains visible behinbd the form and is updated respective of the buttons selected as part of the processing and submission.
The user form opens, service frame 1 is revealed, the user enters the data and presses [+]
to continue adding services by revealing the next service range in the sequence (#2 "frm_s2").
Code:
Private Sub cbt_s1_add_Click()
Me.cbt_s1_add.Enabled = False
Me.btn_help.Visible = False
Me.cbt_s1_del.Visible = True
trnsvc_add Me, 1 '(proceduree, uf:frmservice, index)
End Sub
The problem resides in this code (trnsvc_add), specifically the section highlighted in orange.
Rich (BB code):
Sub trnsvc_add(frmservice As Object, index As Long)
Debug.Print srow
Debug.Print cd_rrow
ridno = ws_master.Cells(srow, 1)
'run data check looking for missing information
trn_srv_datachk frmservice, index
'index is "service" number, frame number
If index = 1 Then
cdsvc_col = 38
msrv_col = 13
c_ul = 3 'cell unlock - number of cells to unlock after populated
ElseIf index = 2 Then
cdsvc_col = 45
msrv_col = 14
c_ul = 2
ElseIf index = 3 Then
cdsvc_col = 52
msrv_col = 15
c_ul = 1
ElseIf index = 4 Then
cdsvc_col = 59
msrv_col = 16
c_ul = 0
ElseIf index = 5 Then
cdsvc_col = 66
msrv_col = 13
c_ul = 0
ElseIf index = 6 Then
cdsvc_col = 73
msrv_col = 14
c_ul = 0
ElseIf index = 7 Then
cdsvc_col = 80
msrv_col = 15
c_ul = 0
Else 'index = 8 Then
cdsvc_col = 87
msrv_col = 16
c_ul = 0
End If
'core_data is a hidden worksheet that is considered the "database: and holds the values of the userform controls
'update core_data
With ws_cd
.Unprotect
If frmservice.Controls("cbx_s" & index & "_rln").Value = True Then
.Cells(cd_rrow, cdsvc_col) = "RLN"
st_msg = "Reline"
Else
.Cells(cd_rrow, cdsvc_col) = "CHG"
st_msg = "Change"
End If
.Cells(cd_rrow, cdsvc_col + 1) = frmservice.Controls("tb_s" & index & "_lwr").Value
.Cells(cd_rrow, cdsvc_col + 2) = frmservice.Controls("tb_s" & index & "_upr").Value
.Cells(cd_rrow, cdsvc_col + 3) = frmservice.Controls("cb_s" & index & "_crew").Value
.Cells(cd_rrow, cdsvc_col + 4) = frmservice.Controls("cb_s" & index & "_div").Value
.Cells(cd_rrow, cdsvc_col + 5) = frmservice.Controls("cb_s" & index & "_base").Value
.Cells(cd_rrow, cdsvc_col + 6) = frmservice.Controls("cb_s" & index & "_pitch").Value
.Protect
End With
'ws_master is the worksheet behind the form that this code updates
'update master: PDA bookings
With ws_master
mbevents = False
.Unprotect
.Range("M" & srow & ":P" & srow).Interior.Color = RGB(166, 166, 166)
With .Cells(srow, msrv_col)
.Value = frmservice.Controls("cb_s" & index & "_crew").Value
.Interior.ColorIndex = 0
End With
If c_ul = 0 Then
.Range("M" & srow & ":P" & srow).locked = False
Else
For L1 = msrv_col To msrv_col + c_ul
.Cells(srow, L1).locked = False
Next L1
End If
'update master: PDA services
'determine destination row for service entry
svc_lrow = Application.WorksheetFunction.Match("Facility Maintenance Activities", .Columns(1)) - 3 'last empty row before a new line has to be inserted
Set rng_cntb = .Range("A13:A" & svc_lrow) 'service range
er = Application.WorksheetFunction.CountBlank(rng_cntb)
If er = 0 Then 'no empty rows in PDA service range insert a row
MsgBox "Not enough room. Row added at " & svc_lrow + 1
.Range("A" & svc_lrow + 1 & ":R" & svc_lrow + 1).Insert shift:=xlDown
svc_drw = svc_lrow + 1
Else
svc_drw = rng_cntb.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
End If
'add static data
Set rng_cpy = .Range("A" & srow & ":G" & srow)
rng_cpy.Copy .Range("A" & svc_drw)
'populate service data range (M-Q)
.Range("H" & svc_drw & ":Q" & svc_drw).Interior.Color = RGB(166, 166, 166)
With .Cells(svc_drw, msrv_col)
.Value = frmservice.Controls("cb_s" & index & "_crew").Value
.Interior.ColorIndex = 0
End With
'populate dispatch
With .Cells(svc_drw, 2)
tr_msg = frmservice.Controls("tb_s" & index & "_lwr").Value & "-" & frmservice.Controls("tb_s" & index & "_upr").Value
.Value = st_msg & " " & tr_msg
.Font.Bold = True
.WrapText = True
.HorizontalAlignment = xlCenter
.Font.Color = vbBlack
End With
'format svc row
.Rows(svc_drw).AutoFit
.Range(.Cells(svc_drw, 1), .Cells(svc_drw, 17)).VerticalAlignment = xlCenter
.Protect
mbevents = True
End With
ftr = index + 1
frmservice.Controls("frm_service" & ftr).Visible = True
End Sub
The issue:
After the press of the [+] button, worksheet core_data updates as coded.
The code in purple properly executes and provides the desired updates to that worksheet
The code in orange does NOT populate/format as coded IN RUNTIME! If I step through this code using F8, it works. Weird!
The next service frame (#2) is revealed allowing the user to enter data for that service.
It weirds out again here ... again, only in run time. Not with stepping through.
Pressing the [+] in service 2's frame will update core_data properly, the code in purple (respective of servic 2 data)... BUT ... now the update to worksheet master for service 1's orange code appears! (this should have been revealed as part of pressing [+] in service 1's frame. The orange code for service 2 does not update ws_master with service 2 data.
Here is the code for service 2's [+] button, mind you the update described above occurs before this code is encountered. (ie it updates then stops at the stop line)
Code:
Private Sub cbt_s2_add_Click()
Stop
Me.cbt_s2_add.Enabled = False
trnsvc_add Me, 2
End Sub
My project is several 100s of lines of code and utilizes data from several independent and closed workbooks to work. For that, it isn't possible for me to provide the file to experiment with unfortunately. I hope its a simple error as it has put my project in a hold. I hope I've provided appropriate and sufficient information. If not, please ask.