Link to home
Start Free TrialLog in
Avatar of Xtophe_S
Xtophe_SFlag for Belgium

asked on

Values on inputsheet change unwanted when an option is selected

Hi,

I'm adding functionality on on a "financial simulation analysis" application
it's called Insight (c) , and simulates/re-rates traffic from calls and SMS mesages, for profitability purposes. It is able to compare 7 scenarios which all operate independently

All input is steered from forms, and input from the forms are inserted in an input sheet in the background.
This way, when simulation is running, it uses this input as parameters which steers calculation based upon a multitude of parameters. ( actually a small billing engine for mobile telecom)

Reading values of the  "bundle option code" works fine, but in the mean time other values, meaning the ranges used during "initialisation" are changed into "false" , while they should remain as the were, as these variables serve for other purposes.

Creating additional forms is not a good option, as those type forms eat a lot of memory. (because of the checkboxes, see pic's).
To save memory and keep performance, I'm bound to re-use the same forms both for input of "traffic time validity", and "bundle time validity"

Can anyone tell me what I need to do to prevent that cells are changed unexpectedly ( as indicated in the pictures. ?

code snippet userform init

Private Sub UserForm_Initialize()
    
    Dim ws As Worksheet
    Dim iStyle As Long
    Dim hWndForm As Long
    
    hWndForm = FindWindow("ThunderDFrame", Me.Caption)

    
    iStyle = GetWindowLong(hWndForm, GWL_STYLE)
    iStyle = iStyle Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX
    SetWindowLong hWndForm, GWL_STYLE, iStyle
    
    '/////////Show the window with the changes ///////////
    ShowWindow hWndForm, SW_SHOW
    DrawMenuBar hWndForm
    SetFocus hWndForm
    
    Set ws = ThisWorkbook.Sheets("INPUT")
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    
        With Day_Time_Dimension_SC0
            
            '/////////// Read Voice fields ////////////////
            
            .SC0_VD_MON_Base = ws.Range("K129").Value
            .SC0_VD_TUE_Base = ws.Range("K138").Value
            .SC0_VD_WED_Base = ws.Range("K147").Value
            .SC0_VD_THU_Base = ws.Range("K156").Value
            .SC0_VD_FRI_Base = ws.Range("K165").Value
            .SC0_VD_SAT_Base = ws.Range("K174").Value
            .SC0_VD_SUN_Base = ws.Range("K183").Value
            
            .SC0_VD_MON_Prox = ws.Range("K130").Value
            .SC0_VD_TUE_Prox = ws.Range("K139").Value
            .SC0_VD_WED_Prox = ws.Range("K148").Value
            .SC0_VD_THU_Prox = ws.Range("K157").Value
            .SC0_VD_FRI_Prox = ws.Range("K166").Value
            .SC0_VD_SAT_Prox = ws.Range("K175").Value
            .SC0_VD_SUN_Prox = ws.Range("K184").Value
            
            .SC0_VD_MON_Mobi = ws.Range("K131").Value
            .SC0_VD_TUE_Mobi = ws.Range("K140").Value
            .SC0_VD_WED_Mobi = ws.Range("K149").Value
            .SC0_VD_THU_Mobi = ws.Range("K158").Value
            .SC0_VD_FRI_Mobi = ws.Range("K167").Value
            .SC0_VD_SAT_Mobi = ws.Range("K176").Value
            .SC0_VD_SUN_Mobi = ws.Range("K185").Value
end with
end sub

Open in new window



snippet to read out the "bundle option"

Private Sub SC0_OptionsChoice_Change()

Dim Val As Variant
Dim ws As Worksheet


Set ws = ThisWorkbook.Sheets("INPUT")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Val = SC0_OptionsChoice.Value

Select Case Val
    Case Is = "Traffic Options"
        Call UserForm_Initialize
    
    
    Case Is = "Bundle Options"

 
            '/////////// Read Voice fields ////////////////
            
            SC0_VD_MON_Base = ws.Range("K260").Value
            SC0_VD_TUE_Base = ws.Range("K269").Value
            SC0_VD_WED_Base = ws.Range("K278").Value
            SC0_VD_THU_Base = ws.Range("K287").Value
            SC0_VD_FRI_Base = ws.Range("K296").Value
            SC0_VD_SAT_Base = ws.Range("K305").Value
            SC0_VD_SUN_Base = ws.Range("K314").Value
            
            SC0_VD_MON_Prox = ws.Range("K261").Value
            SC0_VD_TUE_Prox = ws.Range("K270").Value
            SC0_VD_WED_Prox = ws.Range("K279").Value
            SC0_VD_THU_Prox = ws.Range("K288").Value
            SC0_VD_FRI_Prox = ws.Range("K297").Value
            SC0_VD_SAT_Prox = ws.Range("K306").Value
            SC0_VD_SUN_Prox = ws.Range("K315").Value
            
            SC0_VD_MON_Mobi = ws.Range("K262").Value
            SC0_VD_TUE_Mobi = ws.Range("K271").Value
            SC0_VD_WED_Mobi = ws.Range("K280").Value
            SC0_VD_THU_Mobi = ws.Range("K289").Value
            SC0_VD_FRI_Mobi = ws.Range("K298").Value
            SC0_VD_SAT_Mobi = ws.Range("K307").Value
            SC0_VD_SUN_Mobi = ws.Range("K316").Value
    case else
end select
end sub

Open in new window

Initial-status-traffic-input-fie.jpg
after-changing-to-Bundle-status-.jpg
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

There's not much to go on there so at a guess: do you have code behind the controls that you are initialising in that code? If so, it's probably being triggered when you set up the initial values.
Avatar of Xtophe_S

ASKER

I took a copy of the input sheet and a copy of that specific form and all the code of the form behind it in another workbook.

Hope this helps you out, ...

/Xtophe
Sample-Input-and-form.xlsm
it is the code behind the listbox ( at the bottom left of the form) which causes problems.

when i change it to "Bundle options", then something triggers unwanted changes  as indicated in the pics given yesterday.

/Xtophe
when you change to "Bundle options".

This routine is called:
then within this routine,When this statement is executed...
SC0_VD_MON_Base = ws.Range("K260").Value

Private Sub SC0_OptionsChoice_Change()

That triggers a call to SCO_VD_MON_Base_Click()

SCO_VD_MON_Base_Click has this code:
Private Sub SC0_VD_MON_Base_Click()
    
    Dim Val As Boolean
    Dim ws0 As Worksheet
    Set ws0 = ThisWorkbook.Sheets("INPUT")
    
    Val = SC0_VD_MON_Base.Value
    
    Select Case Val
        Case Is = "True"
            ws0.Range("K129") = "TRUE"
            
        Case Is = "False"
            ws0.Range("K129") = "FALSE"
            
        Case Else
            ws0.Range("K129") = "TRUE"
            
    End Select
End Sub

Open in new window

The above code changes the value in K129 to False. The Net effect is that because K260 is false... K129 gets set to false and so on.

If you do NOT want to trigger events when executing
Private Sub SC0_OptionsChoice_Change() you could consider adding something like Application.EnableEvents = False then setting it back to true when done.

This is a good resource for suppressing events if you decide to go that route
http://www.cpearson.com/excel/Events.aspx
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok thank you for your advice, ...
It appears to be working.

I' ve changed folowing:

OptionChoice code

Private Sub SC0_OptionsChoice_Change()

Dim Val As Variant
Dim ws As Worksheet
Dim bSkipEvents As Boolean

If bSkipEvents Then
    Exit Sub
End If

Set ws = ThisWorkbook.Sheets("INPUT")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

bSkipEvents = True
 
 If bSkipEvents = True Then

    Val = SC0_OptionsChoice.Value
    
    Select Case Val
        Case Is = "Traffic Options"
            Call UserForm_Initialize
        
        
        Case Is = "Bundle Options"
    
     
                '/////////// Read Voice fields ////////////////
                
                SC0_VD_MON_Base = ws.Range("K260").Value
                SC0_VD_TUE_Base = ws.Range("K269").Value
                SC0_VD_WED_Base = ws.Range("K278").Value
                SC0_VD_THU_Base = ws.Range("K287").Value
                SC0_VD_FRI_Base = ws.Range("K296").Value
                SC0_VD_SAT_Base = ws.Range("K305").Value
                SC0_VD_SUN_Base = ws.Range("K314").Value
          ' .......... ( deleted rest of code for readability :)     
           
        Case Else
    
    End Select
Else
End If


End Sub

Open in new window



Link to  SC0_VD_MON_Base

Private Sub SC0_VD_MON_Base_Click()

Dim bSkipEvents As Boolean
If bSkipEvents = False Then
    Exit Sub
End If

bSkipEvents = True

    Dim Val As Boolean
    Dim ws0 As Worksheet
    Set ws0 = ThisWorkbook.Sheets("INPUT")
    
    Val = SC0_VD_MON_Base.Value
    
    Select Case Val
        Case Is = "True"
            ws0.Range("K129") = "TRUE"
            
        Case Is = "False"
            ws0.Range("K129") = "FALSE"
            
        Case Else
            ws0.Range("K129") = "TRUE"
            
    End Select
    
    
bSkipEvents = False

End Sub

Open in new window



This way the value in "K129" is not changing unexpectedly. and when changing back to traffic options.
I added an "if clause" in between for other code which needs to be added later, but the principle is the same as you suggested :)

I tried also application.enable events, but that did not work, though thank you for the effort.

Now I need to add this in all click and change events :)

Thank You
/Xtophe