Scrollbar changing userform textbox and cell in a sheet. (number only increases)

[finaljustice]

Hello,

I am creating a userform to manipulate a sheet and expose only some information of the sheet to the user.

Basically I have a cell, on this example A1, where it is supposed to be a temperature and that temperature will affect many other calculations on the sheet (for simplification the example placed here doesn't involve the complete sheet with calculations).

This temperature must vary between 1600ºC and 1800ºC and must be displayed and altered on a userform textbox an cells.

I came up with this code:

Private Sub UserForm_Initialize()UserForm1.TextBox1 = ThisWorkbook.Sheets(1).Range("A1")
End Sub

Private Sub ScrollBar1_Change()
[FONT=arial]'the idea here is to be able to vary between 1600ºC and 1800ºC freely, and always start from what is on cell A1, and when altered, cell A1 will be altered as well...[/FONT]
With ScrollBar1
    .Min = UserForm1.TextBox1
    .Max = 1850
    .LargeChange = 50
    .SmallChange = 1
    '.Value = UserForm1.TextBox1
End With


Sheets(1).Range("A1").Value = ScrollBar1.Value
UserForm1.TextBox1 = Sheets(1).Range("A1")
End Sub

On this example above I am having issues, since I am only being able to increase the temperature. I therefore realized that the reason must be:

.min = Userform1.Textbox1

On the other hand, if I place the value for the minimum at 1600, then when ever I click on the button, the textbox resets to that minimum. I don't want that, I would like the value to be the one that was last used/calculated on the spreadsheet cell A1, because that would "reset" all the other calculations that would be dependent on this cell.

Could someone help we with this, I think the solution must be simple, I'm just not getting the logic of how to make it behave as I want it.

Thanks for your attention
Yours sincerely.
Luis

 

[mikerickson]

The problem is the events that you are using.
You don't want the Max and Min to be set every time the ScrollBar changes value. You want them set once and then use the scrollbar.

So all that initializing stuff should go in the Initialize event.

Private Sub UserForm_Initialize()
    With UserForm1
        With .ScrollBar1
            .Min = 1600
            .Max = 1850
            .LargeChange = 50
            .SmallChange = 1
            '.Value = UserForm1.TextBox1
        End With
        DisableUFEvents = True
        .TextBox1 = ThisWorkbook.Sheets(1).Range("A1").Text
        DisableUFEvents = False
    End With
End Sub

I'm aslo guessing that you want the TextBox and ScrollBar values to be linked. Note the module level DisableUFEvents variable that keeps events from cascading.

Dim DisableUFEvents As Boolean

Private Sub ScrollBar1_Change()
    If DisableUFEvents Then Exit Sub
    DisableUFEvents = True
    Me.TextBox1.Text = Me.ScrollBar1.Value
    DisableUFEvents = False
End Sub

Private Sub TextBox1_Change()
    If DisableUFEvents Then Exit Sub
    DisableUFEvents = True
    Me.ScrollBar1.Value = Val(Me.TextBox1.Text)
    DisableUFEvents = False
End Sub

 

[finaljustice]

The problem is the events that you are using.
You don't want the Max and Min to be set every time the ScrollBar changes value. You want them set once and then use the scrollbar.

So all that initializing stuff should go in the Initialize event.

Private Sub UserForm_Initialize()
    With UserForm1
        With .ScrollBar1
            .Min = 1600
            .Max = 1850
            .LargeChange = 50
            .SmallChange = 1
            '.Value = UserForm1.TextBox1
        End With
        DisableUFEvents = True
        .TextBox1 = ThisWorkbook.Sheets(1).Range("A1").Text
        DisableUFEvents = False
    End With
End Sub

I'm aslo guessing that you want the TextBox and ScrollBar values to be linked. Note the module level DisableUFEvents variable that keeps events from cascading.

Dim DisableUFEvents As Boolean

Private Sub ScrollBar1_Change()
    If DisableUFEvents Then Exit Sub
    DisableUFEvents = True
    Me.TextBox1.Text = Me.ScrollBar1.Value
    DisableUFEvents = False
End Sub

Private Sub TextBox1_Change()
    If DisableUFEvents Then Exit Sub
    DisableUFEvents = True
    Me.ScrollBar1.Value = Val(Me.TextBox1.Text)
    DisableUFEvents = False
End Sub

Hello mikerichardson,

Thank you for your answer, I've tried implementing your solution. I have realized that it won't send back the data to the sheet so I basically added another line of code.

Private Sub ScrollBar1_Change()    If DisableUFEvents Then Exit Sub
    DisableUFEvents = True
    Me.TextBox1.Text = Me.ScrollBar1.Value
    DisableUFEvents = False
    Sheets(1).Range("A1").Value = ScrollBar1.Value ' this is what I've added.
End Sub

So this code works exactly how I wanted to, so thank you very much!
Could you please explain the use of this "DisableUFEvents" I've never seen nor have I used this and it is not very clear what your logic there was. I just understood what you meant when you said. "You don't want the Max and Min to be set every time the ScrollBar changes value. You want them set once and then use the scrollbar.
So all that initializing stuff should go in the Initialize event."


And now it is clear what I was doing wrong, but I'm not confident on replicating or using such a solution because I have no clue what the "DisableUFEvents" and how to use it.

I would appreciate if you could take a little extra time to explain me this for learning purposes.

Thank you for your attention and time.
Luis

 

[mikerickson]

The code has it so that when the ScrollBar is changed that will change the TextBox.
That fires the TextBox_Change event, which fires the ScrollBar_Change event, which changes the TextBox... around and around .

The DisableUFEvents variable, together with the If DisableUFEvents The Exit Sub lines, breaks that cascading sequence.

 

[finaljustice]

The code has it so that when the ScrollBar is changed that will change the TextBox.
That fires the TextBox_Change event, which fires the ScrollBar_Change event, which changes the TextBox... around and around .

The DisableUFEvents variable, together with the If DisableUFEvents The Exit Sub lines, breaks that cascading sequence.

I think I get it... It acts like a switch. Very interesting way set it up, i've never seen this "cascading" effect, I've always been more "linear"... nice, I'll try and absorb this.

I appreciate the time and effort on explaining this, haven't seen a post about this subject before, hope this also helps others!!


Thanks once again!
Luis

 

[finaljustice]

The code has it so that when the ScrollBar is changed that will change the TextBox.
That fires the TextBox_Change event, which fires the ScrollBar_Change event, which changes the TextBox... around and around .

The DisableUFEvents variable, together with the If DisableUFEvents The Exit Sub lines, breaks that cascading sequence.

Hello Mike,

I've been working on this project where I used the scrollbar solution presented above.
It has been working perfectly until now, let me explain you what is happening. Maybe you can help me figure this out.

So basically I have created a userform (Userform1) that has all these scrollbars that allows the user to manipulate the values accordingly and see what happens in the interface I created. As the project grew, I had the idea of creating another userform (userform2, since the screen was already a little cluttered) that has extra information that must change while scrolling on the scrollbars. So if the user wanted to see in greater detail this information, there is a button that pops open the userform 2 with the extra information.

Both userforms were working perfectly as long as they were not open at the same. (Set the variables with the scrollbar in userform1, hit the button to show userform 2 view numbers, close userform2, manipulate again userform1 with the scrollbars... and so on...) Since the idea was to display this information and see the values change as the user manipulates the scrollbars, I researched that I had to set the userforms as Modeless.

After I did this, when I manipulate userform1 nothing wrong happens, but when I open userform2, if I tried changing values on userform 1 (scrolling) or just closing userform2 after I had opened it, I began getting an error.

When debugging the first issue was on the Scrollbar code for example:

[COLOR=#333333]Me.TextBox1.Text = Me.ScrollBar1.Value [/COLOR]

I figured, since there were 2 userforms open, the issue was with the ME, therefore I changed all of them for "Userform1".

Then I tried again, now the error is 'Run Time Error '380': Could not set the value property. Invalid value property' on the Textbox code on the line:

[/COLOR][COLOR=#333333]Userform1.ScrollBar1.Value = Val(Userform1.TextBox1.Text)[/COLOR][COLOR=#333333]

I tried with no avail:

[/COLOR] UserForm1.ScrollBar1.Value = UserForm1.TextBox1.Value[COLOR=#333333]

These are only examples since there are 12 different scrollbars.

Since this is the first time I'm using modeless userforms I am a little lost. Could you shine a light on what it could be?
Thank you,
Luis

 

[mikerickson]

I'm afraid that I can't help with this. My Mac doesn't support modeless user forms.