Aaaargh, I just cannot work this out...

T

towners

Board Regular
Joined
Mar 12, 2009
Messages
225
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Sorry for the vague title, I can't sum up my problem in a sentance. I'll do my best to explain in the hope that someone can help save my sanity...

I have an application that I’m 99% finished writing but I have this problem that I’ll try to describe that’s driving me crazy…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
My application is designed for users to input production data based on a cell, an operator and the units processed. It has 3 userforms; “Master” that opens when the workbook starts and displays three dynamic charts (using the save as .gif routine) and three command buttons to launch either “Cell data entry”, “Resource Entry” or “Exit”
<o:p></o:p>
Resource entry allows the user to add operators, cells, or activities and populates individual “Cell”, “Operator” or “Activity” worksheets. The userform has validations built into events to ensure data entry integrity. The “Cell Data Entry” =userform displays a series of text boxes or list boxes. The list boxes rowsource relates to the Dynamic Name Reference for either “Operator”, “Cell” or “Activity”. Again there are validations behind each entry field to ensure data entry integrity. The “exit” command resets excel and saves and closes the workbook.
<o:p></o:p>
There are 7 entry fields on the “Cell Data Entry” userform with command buttons for “Enter”, “Clear”, “Modify”, “Delete” and “Close”. The “Enter” command button is only enabled when all fields have been completed and passed their own validation routine.
<o:p></o:p>
Once the operator has input Cell production data he will click “Close” the “Cell Data Entry” userform closes and the “Master” userform is activated and the charts refreshed and displayed.
<o:p></o:p>
To “poka yoke” or mistake proof the data entry some fields are set dependant on the value in another, for example if the user enters a “Quantity” then the “Activity” list box is automatically set to “Deconstruction” as opposed to other options such as “Training” or “Cleaning” and the list box is disabled, likewise if the user enters a “Quantity” of “0” the selection “Deconstruction” cannot be selected.
<o:p></o:p>
My problem occurs with setting the value of the listbox “Activity”…
<o:p></o:p>
I use the code “lbo_Activity.value = “Deconstruction”” to set the listbox however the list box value isn’t always set and I can’t work out why?
<o:p></o:p>
I’ve done extensive testing and gone right through my code, what I’ve found is that I will open my application and the listbox value will be set and therefore entered into my background worksheet fine. I can close and open the “Cell Data Entry” userform and repeat the process as many time as I like and the listbox value is set fine. Then, I exit the application, re-open it, open the “Cell Data Entry” userform and the listbox value will not be set. I close the application, re-open it and the list box entry value is set, close the application, re-open it and the list box value is not set.

So, in summary this repeats like so:
<o:p></o:p>
Open application, listbox value set = YES, close application<o:p></o:p>
Open application, listbox value set = NO, close application<o:p></o:p>
Open application, listbox value set = YES, close application<o:p></o:p>
Open application, listbox value set = NO, close application<o:p></o:p>
And so on.
<o:p></o:p>
What I cannot fathom is how, when I close the application, close excel, and even re-boot my computer this occurs. I make some settings i.e. application.visible = false; application.calculate = xlmanual and application.screenupdating = false when the workbook opens but I reset these when the workbook closes.
<o:p></o:p>
Can anyone provide any advice or pointers on where I should look?<o:p></o:p>
I’m using Win 7 and excel 2010.
<o:p></o:p>
I’ve posted relevant code extracts if they help.

Workbook open code
<o:p>
Code: 
</o:p><o:p>Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.Visible = False
Application.Calculation = xlCalculationManual
Master.Show
End Sub
</o:p>
<o:p>Code in module 1</o:p>
<o:p>
Code: 
</o:p>
<o:p>Option Explicit</o:p>
<o:p>Private Declare Function GetSystemMenu Lib "user32" _
(ByVal hWnd As Long, _
ByVal bRevert As Long) As Long
 
Private Declare Function RemoveMenu Lib "user32" _
(ByVal hme2nu As Long, _
ByVal nPosition As Long, _
ByVal wFlags As Long) As Long
 
Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName2 As String, _
ByVal lpWindowName2 As String) As Long
 
Private Const MF_BYPOSITION As Long = &H400
 
Public Public_Password As Integer
Public Public_Modify As Integer
Public Public_RecordNumber As Integer
Public Public_CellRecordNumber As Integer
Public Public_CellTitle As Long
Public Public_CellUnitsPerDay As Integer
Public Public_DailyMinsRun As Integer
Public Public_UnitAvKg As Integer
Public Public_OperatorRecordNumber As Integer
Public Public_Operator As Long
Public Public_EmployedMinutes As Long
Public Public_BreakMinutes As Long
Public Public_ActivityRecordNumber As Integer
Public Public_Activity As Long
Public Public_Date As Long
Public Public_TimeOn As Long
Public Public_TimeOff As Long
Public Public_TimeDiff As Long
Public Public_QtyActivity As Long
Public Public_TurnOnEnter As Long
Public Public_TurnOnClear As Integer
Public Public_LastRecordNumber As Integer
Public Public_NewRecordNumber As Integer
</o:p>
<o:p>"Master" userform code - initialize</o:p>
<o:p>
Code: 
</o:p>
<o:p>Private Sub Userform_Initialize()</o:p>
<o:p>Dim lFrmHdl As Long, iCount As Integer
lFrmHdl = FindWindowA(vbNullString, Me.Caption)</o:p>
<o:p>If lFrmHdl <> 0 Then</o:p>
<o:p>For iCount = 0 To 2
RemoveMenu GetSystemMenu(lFrmHdl, False), 0, MF_BYPOSITION
Next iCount
End If</o:p>
<o:p>Dim B As Boolean
B = HideCloseButton(UF:=Me)</o:p>
<o:p> With Frame1
        .Left = 0
        .Top = 0
        .Width = Me.MultiPage1.Width
        .Height = Me.MultiPage1.Height
        .BorderStyle = fmBorderStyleNone
        .Caption = ""
        .BackColor = vbWhite
    End With</o:p>
<o:p>Application.Calculate
ActiveWorkbook.RefreshAll
End Sub
Private Sub Userform_Activate()</o:p>
<o:p>Public_Password = 0</o:p>
<o:p>ChartNum = 1
UpdateChart_OverallOEE
UpdateChart_OverallUnits
UpdateChart_OverallWeights
</o:p>
<o:p>"Cell Data Entry" Code - initialize & activate</o:p>
<o:p>
Code: 
Private Sub Userform_Initialize()
Dim lRow As Long
Dim lRng As String
Dim nRng As String</o:p>
<o:p> Sheets("Cells").Select</o:p>
<o:p>     'dimension the length of the range
        lRow = Range("A2:A1048576").Find(What:="", LookAt:=xlWhole).Row - 1</o:p>
<o:p>         'sort the worksheet for celltitle alphabetically
            Columns("A:J").Select
            ActiveWorkbook.Worksheets("Cells").Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("Cells").Sort.SortFields.Add Key:=Range("B2:B" & lRow), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets("Cells").Sort
                .SetRange Range("A1:J" & lRow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With</o:p>
<o:p> Sheets("Operator").Select
 
        'dimension the length of the range
        lRow = Range("A2:A1048576").Find(What:="", LookAt:=xlWhole).Row - 1</o:p>
<o:p>         'sort the worksheet for operator alphabetically
            Columns("A:G").Select
            ActiveWorkbook.Worksheets("Operator").Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("Operator").Sort.SortFields.Add Key:=Range("B2:B" & lRow), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets("Operator").Sort
                .SetRange Range("A1:G" & lRow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
 
    Sheets("Activity").Select
 
        'dimension the length of the range
        lRow = Range("A2:A1048576").Find(What:="", LookAt:=xlWhole).Row - 1</o:p>
<o:p>         'sort the worksheet for activity alphabetically
            Columns("A:C").Select
            ActiveWorkbook.Worksheets("Activity").Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("Activity").Sort.SortFields.Add Key:=Range("B2:B" & lRow), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets("Activity").Sort
                .SetRange Range("A1:C" & lRow)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With</o:p>
<o:p>cmd_Enter.Enabled = False</o:p>
<o:p>
</o:p><o:p>txt_Date = Format(Date - 1, "dd/mm/yyyy")
txt_Qty = 0
lbo_CellTitle.SetFocus</o:p>
<o:p>Public_Modify = 0
Public_CellTitle = 0
Public_Operator = 0
Public_CellRecordNumber = 0
Public_CellUnitsPerDay = 0
Public_OperatorRecordNumber = 0
Public_Operator = 0
Public_EmployedMinutes = 0
Public_BreakMinutes = 0
Public_ActivityRecordNumber = 0
Public_Activity = 0
Public_Date = 0
Public_TimeOn = 0
Public_TimeOff = 0
Public_TimeDiff = 0
Public_QtyActivity = 0
Public_TurnOnEnter = 0
Public_TurnOnClear = 0
Public_LastRecordNumber = 0
Public_NewRecordNumber = 0
Public_PeriodNumber = 0</o:p>
<o:p>lbl_Message1 = ""
lbl_Message2 = ""
lbl_Message3 = ""
lbl_Message4 = ""
lbl_Message5 = ""
lbl_Message6 = ""
lbl_message7 = ""</o:p>
<o:p>Sheets("CellData").Select</o:p>
<o:p> lRow = Range("B2:B1048576").Find(What:="", LookAt:=xlWhole).Row - 1
        rng = "B" & lRow
            Range(rng).Select
                Public_LastRecordNumber = ActiveCell.Value</o:p>
<o:p>lbl_Message2.Caption = " Previous record: " & Public_LastRecordNumber & " "
lbl_Message3.Caption = " " & ActiveCell.Offset(0, 1).Value & "," & Format(ActiveCell.Offset(0, 2).Value, "dd/mm/yyyy") & " "
lbl_Message4.Caption = " " & ActiveCell.Offset(0, 3).Value & "," & Format(ActiveCell.Offset(0, 5).Value, "hh:mm") & "," & Format(ActiveCell.Offset(0, 6).Value, "hh:mm") & "," & ActiveCell.Offset(0, 7).Value & "," & ActiveCell.Offset(0, 8).Value & " "</o:p>
<o:p>End Sub
</o:p>
<o:p>"Cell Data Entry" Code - example of validation routine</o:p>
<o:p>
Code: 
</o:p>
<o:p>Private Sub txt_qty_Exit(ByVal Cancel As MSForms.ReturnBoolean)</o:p>
<o:p> If Not IsNumeric(txt_Qty) Then
        lbl_Message5 = "Please enter a whole number"
        txt_Qty = 0
        txt_Qty.SetFocus
        Public_QtyActivity = 0
            Else
        lbl_Message1 = ""
        lbl_Message5 = ""
    End If</o:p>
<o:p> If txt_Qty > 0 Then
 
        lbo_Activity = "Deconstruction"
        lbo_Activity.Enabled = False
    End If
 
    If txt_Qty = 0 Then
        lbo_Activity.Enabled = True
    End If</o:p>
<o:p> If lbo_Activity = "Deconstruction" And txt_Qty = 0 Then
            lbl_Message1 = "Please enter a quantity if Activity is 'Deconstruction' or select a different Activity if quantity is 0"
            Public_QtyActivity = 0
            txt_Qty.SetFocus
        Else
            Public_QtyActivity = 111111
            lbl_Message1 = ""
    End If</o:p>
<o:p>Public_TurnOnEnter = Public_CellTitle + Public_Operator + Public_TimeOn + Public_TimeOff + Public_TimeDiff + Public_QtyActivity
    If Public_TurnOnEnter = 123456 Then
        cmd_Enter.Enabled = True
    Else
        cmd_Enter.Enabled = False
    End If</o:p>
<o:p>End Sub
</o:p>
<o:p>"Cell Data Entry" code - Press Enter</o:p>
<o:p>
Code: 
</o:p>
<o:p>Private Sub cmd_Enter_Click()</o:p>
<o:p>Do While Public_Modify = 1</o:p>
<o:p>     ActiveCell.Offset(0, 1).Value = lbo_CellTitle.Value
        ActiveCell.Offset(0, 2).Value = txt_Date.Value
        ActiveCell.Offset(0, 3).Value = lbo_Operator.Value
        ActiveCell.Offset(0, 5).Value = txt_TimeOn.Value
        ActiveCell.Offset(0, 6).Value = txt_TimeOff.Value
        ActiveCell.Offset(0, 7).Value = txt_Qty.Value
        ActiveCell.Offset(0, 8).Value = lbo_Activity.Value
 
        Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True</o:p>
<o:p> GoTo ResetForm
Loop
 
Dim firstclearrow As Long
Dim lRow As Long
Dim yRng As Range
Dim pRng As Range
Dim Yn As Range
Dim Pn As Range
Dim dt As Date
Dim Fy As Boolean
Dim Fp As Boolean
Dim Period As Integer
Dim AccYear As Integer</o:p>
<o:p>'lookup the period and year</o:p>
<o:p>dt = txt_Date</o:p>
<o:p>Sheets("Periods").Select</o:p>
<o:p>Set yRng = ActiveWorkbook.Names("Periods_AccYear").RefersToRange
 Set pRng = ActiveWorkbook.Names("Periods_PeriodNumber").RefersToRange</o:p>
<o:p> For Each Yn In yRng
       If Yn(, -2) <= dt And Yn(, -1) >= dt Then
        AccYear = Yn
            Fy = True
             Exit For
        End If
    Next Yn
 
    For Each Pn In pRng
       If Pn(, 2) <= dt And Pn(, 3) >= dt Then
        Period = Pn
            Fp = True
             Exit For
        End If
    Next Pn</o:p>
<o:p>Sheets("CellData").Select</o:p>
<o:p> lRow = Range("B2:B1048576").Find(What:="", LookAt:=xlWhole).Row - 1
        rng = "B" & lRow
            Range(rng).Select
                Public_LastRecordNumber = ActiveCell.Value
Public_NewRecordNumber = Public_LastRecordNumber + 1
 
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = Public_NewRecordNumber
        ActiveCell.Offset(0, 1).Value = lbo_CellTitle.Value
        ActiveCell.Offset(0, 2).Value = Format(txt_Date.Value, "dd/mm/yyyy")
        ActiveCell.Offset(0, 3).Value = lbo_Operator.Value
        ActiveCell.Offset(0, 4).FormulaR1C1 = "=INDEX(Operator!R2C1:R200C2,MATCH(RC[-1],Operator_Operator,0),1)"
        ActiveCell.Offset(0, 5).Value = txt_TimeOn.Value
        ActiveCell.Offset(0, 6).Value = txt_TimeOff.Value
        ActiveCell.Offset(0, 7).Value = txt_Qty.Value
        ActiveCell.Offset(0, 8).Value = lbo_Activity.Value
        ActiveCell.Offset(0, 9).Value = Period
        ActiveCell.Offset(0, 10).FormulaR1C1 = "=Concatenate(RC[1],RC[-1])"
        ActiveCell.Offset(0, 11).Value = AccYear
        ActiveCell.Offset(0, 12).FormulaR1C1 = "=YEAR(RC[-10])"
        ActiveCell.Offset(0, 13).FormulaR1C1 = "=TEXT(RC[-11],""mmm"")"
        ActiveCell.Offset(0, 14).FormulaR1C1 = "=WEEKNUM(RC[-12],2)"
        ActiveCell.Offset(0, 15).FormulaR1C1 = "=TEXT(RC[-13],""ddd"")"
        ActiveCell.Offset(0, 16).FormulaR1C1 = "=RC[-10]-RC[-11]"
        ActiveCell.Offset(0, 17).FormulaR1C1 = "=(RC[-11]-RC[-12])*24"
        ActiveCell.Offset(0, 18).FormulaR1C1 = "=((RC[-12]-RC[-13])*24)*60"
        ActiveCell.Offset(0, 19).FormulaR1C1 = "=VLOOKUP(RC[-18],Cells!R2C2:R100C10,3,FALSE)"
        ActiveCell.Offset(0, 20).FormulaR1C1 = "=VLOOKUP(RC[-17],Operator!R2C2:R200C10,4,FALSE)"
        ActiveCell.Offset(0, 21).FormulaR1C1 = "=VLOOKUP(RC[-20],Cells!R2C2:R100C10,5,FALSE)"
        ActiveCell.Offset(0, 22).FormulaR1C1 = "=VLOOKUP(RC[-21],Cells!R2C2:R100C10,6,FALSE)"
        ActiveCell.Offset(0, 23).FormulaR1C1 = "=IFERROR(RC[-5]/RC[-16],0)"
        ActiveCell.Offset(0, 24).FormulaR1C1 = "=IF(RC[-17]/RC[-6]=0,0,RC[-17]/RC[-6])"
        ActiveCell.Offset(0, 25).FormulaR1C1 = "=RC[-7]/RC[-6]"
        ActiveCell.Offset(0, 26).FormulaR1C1 = "=IFERROR(RC[-8]/RC[-6],"""")"
        ActiveCell.Offset(0, 27).FormulaR1C1 = "=IFERROR(AVERAGE(RC[-2],RC[-1]),"""")"
        ActiveCell.Offset(0, 28).FormulaR1C1 = "=IFERROR(RC[-7]/RC[-5],"""")"
        ActiveCell.Offset(0, 29).FormulaR1C1 = "=VLOOKUP(RC[-28],Cells!R2C2:R100C10,8,FALSE)"
        ActiveCell.Offset(0, 30).FormulaR1C1 = "=VLOOKUP(RC[-27],Operator!R2C2:R200C10,5,FALSE)"
        ActiveCell.Offset(0, 31).FormulaR1C1 = "=IFERROR(AVERAGE(RC[-2],RC[-1]),"""")"
        ActiveCell.Offset(0, 32).FormulaR1C1 = "=IFERROR(RC[-7]*RC[-4]*RC[-3],"""")"
        ActiveCell.Offset(0, 33).FormulaR1C1 = "=IFERROR(RC[-7]*RC[-5]*RC[-3],"""")"
        ActiveCell.Offset(0, 34).FormulaR1C1 = "=IFERROR(AVERAGE(RC[-2],RC[-1]),"""")"
        ActiveCell.Offset(0, 35).FormulaR1C1 = "=(VLOOKUP(RC[-34],Cells!R2C2:R100C10,7,FALSE)*RC[-28])"
 
        ActiveSheet.Calculate</o:p>
<o:p>Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
 
Range(rng).Select</o:p>
<o:p>ResetForm:</o:p>
<o:p>txt_TimeOn = ""
        txt_TimeOff = ""
        txt_Qty = ""
        lbo_Activity.Enabled = True</o:p>
<o:p>Sheets("CellData").Select</o:p>
<o:p> lRow = Range("B2:B1048576").Find(What:="", LookAt:=xlWhole).Row - 1
        rng = "B" & lRow
            Range(rng).Select
                Public_LastRecordNumber = ActiveCell.Value</o:p>
<o:p>lbo_CellTitle.SetFocus</o:p>
<o:p>lbl_Message1 = ""
lbl_Message5 = ""
lbl_Message6 = ""
lbl_message7 = ""
lbl_Message2.Caption = " Previous record: " & Public_NewRecordNumber & " "
lbl_Message3.Caption = " " & ActiveCell.Offset(0, 1).Value & "," & Format(ActiveCell.Offset(0, 2).Value, "dd/mm/yyyy") & " "
lbl_Message4.Caption = " " & ActiveCell.Offset(0, 3).Value & "," & Format(ActiveCell.Offset(0, 5).Value, "hh:mm") & "," & Format(ActiveCell.Offset(0, 6).Value, "hh:mm") & "," & ActiveCell.Offset(0, 7).Value & "," & ActiveCell.Offset(0, 8).Value & ""</o:p>
<o:p>cmd_Modify.Visible = True
cmd_Delete.Visible = True
spn_CellDataRecordNumber.Visible = True
cmd_Enter.Enabled = False</o:p>
<o:p>Public_Modify = 0
Public_TimeOn = 0
Public_TimeOff = 0
Public_TimeDiff = 0
Public_QtyActivity = 0
Public_TurnOnEnter = 0</o:p>
<o:p>End Sub
</o:p>
<o:p>"Cell Data Entry" userform code - press "Close"</o:p>
Code: 
Private Sub cmd_Close_Click()
Application.Calculate
ActiveWorkbook.RefreshAll
Public_Modify = 0
Public_TimeOn = 0
Public_TimeOff = 0
Public_TimeDiff = 0
Public_QtyActivity = 0
Public_TurnOnEnter = 0
Unload CellDataEntry
Unload Master
Master.Show
End Sub
"Master" userform code - press "Exit"
Code: 
Private Sub cmd_Exit_Click()
    If MsgBox("Save and Exit?", vbQuestion + vbYesNo, "Close Application") = vbYes Then sClose = 1
        If sClose = 1 Then
            Application.Calculation = xlCalculationAutomatic
            Application.ScreenUpdating = True
            ActiveWorkbook.Close SaveChanges:=True
            Else
            Exit Sub
        End If
End Sub
Any help or guidance would be sincerely appreciated.
Many thanks<o:p></o:p>
<o:p></o:p>
Paul<o:p></o:p>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
I don't follow everything that's going on here but be aware that if the textbox txt_qty has focus and then you close your userform, it triggers and runs your txt_qty_Exit procedure as the userform closes.

It looks like that event procedure runs every time the userform closes. So maybe the logic within that procedure is acting like a Toggle for lbo_Activity = "Deconstruction"

Again, I don't fully appreciate all that is happening here but maybe try something like...

Code: 
Private Sub txt_qty_Exit(ByVal Cancel As MSForms.ReturnBoolean)

[COLOR="Green"]' Prevents running on userform close when no quantity has been entered[/COLOR]
[COLOR="Red"]If txt_Qty = vbNullString Then Exit Sub[/COLOR]

 If Not IsNumeric(txt_Qty) Then
        lbl_Message5 = "Please enter a whole number"
        txt_Qty = 0
        txt_Qty.SetFocus
        Public_QtyActivity = 0
            Else
        lbl_Message1 = ""
        lbl_Message5 = ""
    End If
    
 If txt_Qty > 0 Then
 
        lbo_Activity = "Deconstruction"
        lbo_Activity.Enabled = False
[COLOR="Green"]'     End If[/COLOR]
[COLOR="Red"] Else[/COLOR]

[COLOR="Green"]'    If txt_Qty = 0 Then[/COLOR]
        lbo_Activity.Enabled = True
 End If
 
 If lbo_Activity = "Deconstruction" And txt_Qty = 0 Then
            lbl_Message1 = "Please enter a quantity if Activity is 'Deconstruction' or select a different Activity if quantity is 0"
            Public_QtyActivity = 0
            txt_Qty.SetFocus
        Else
            Public_QtyActivity = 111111
            lbl_Message1 = ""
    End If
    
Public_TurnOnEnter = Public_CellTitle + Public_Operator + Public_TimeOn + Public_TimeOff + Public_TimeDiff + Public_QtyActivity
    If Public_TurnOnEnter = 123456 Then
        cmd_Enter.Enabled = True
    Else
        cmd_Enter.Enabled = False
    End If
End Sub
 
Upvote 0
Hi AlphaFrog,

Thanks for the reply. I made the change so thanks for that, but no change. I can close the userform and then re-open it and whatever "state" the value of my activity listbox was in at the original start up (either set or no set) is the state it remains in, nothing changes, it will always remain set or not set until I close and re open the workbook when it toggles...

The results seem to be toggling when I close and save the workbook, when I close and do not save the the workbook "Toggle" effect isn't occuring.

What happens when I save a workbook? Do you know?

Cheers

Paul
 
Upvote 0
Is there any other code in any other procedures or modules that you haven't listed above that does anything with lbo_Activity or txt_Qty? If yes, can you post that code. Is there a lbo_Activity_Click or Change procedure?
 
Upvote 0
I'm checking through the other modules for exactly that now - and trying to test through and narrow down logically what's going on. I made another test, setting another listbox that does not have dependant relationships and the same symptom happens, set, not set, set, not set... Something else is going on here.

I might try starting from a blank workbook and importing code to narrow it down...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Macro need multiple rows per sheet. Not working
Replies
3
Views
467
DanteAmor
DanteAmor
M
  • Question Question
Cleaning up Code
Replies
0
Views
801
MHamid
M
M
  • Question Question
Extract data from Word to Excel
Replies
1
Views
1K
MHamid
M
M
  • Question Question
PDF Extraction VBA Code
Replies
0
Views
1K
MHamid
M
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

Forum statistics

Threads
1,224,560
Messages
6,179,520
Members
452,923
Latest member
JackiG

Share this page

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
Back
Top