Userform modeless and screenupdate = false in excel 2013

[robert_645]

Hello,

In Excel 2013, it was not possible to scroll in a worksheet after using a userform.
With some help from this forum I found the option userform modeless.
This fixed the scrolling problem but creates a new problem: screenupdate = false doesn't work any more.

Can anybody help how to fix the scrolling issue and the screenupdate = false issue
Thanks in advance

 

[Andrew Poulsom]

Why would you need ScreenUpdating = False when scrolling a worksheet? That command only applies to VBA code.

 

[robert_645]

Sorry for my insufficient explanation.
I am using a userform with some optionbuttons.
When one of the optionbuttons is enabled, I fill a sheet with information.
But by using the option userform mode= false, I see how the sheet is being filled.
And that I dont want to see.
I hope it is clear. If not, please let me know

 

[Andrew Poulsom]

Can we see your VBA code that fills the sheet?

 

[robert_645]

When optionbutton1 is entered then:

Private Sub OptionButton1_Click()


Application.ScreenUpdating = False
Application.DisplayAlerts = False


ListBox1.Clear
ListBox2.Clear
OptionButton8.Value = False


If OptionButton1.Value = True Then


Sheets("Temp").Visible = True 'I use a temporary sheet because
Sheets("Temp").Select
Range("A1:Z5000").ClearContents
Range("A1").Select


Dim jaar As String
Dim kolom As String
Dim mytel As Integer
Dim mytel2 As Integer


jaar = "dbo.QHSE_Planning_" & Sheets("Start").Range("C8") + 2012
mytel = 1
mytel2 = 1


SQL_Login.Login_SQL 'function to make a SQL connection




Do While mytel < 2 'I have 2 columns, named KWR1 and KWR2
kolom = "KWR" & mytel


rc.Open "SELECT [" & kolom & "] FROM " & jaar & " WHERE [" & kolom & "] != ''", con
rc.MoveFirst

Do
Range("A" & mytel2) = rc![KWR1]
mytel2 = mytel2 + 1
rc.MoveNext
Loop Until rc.EOF
mytel = mytel + 1
rc.Close


Loop


con.Close
Set rc = Nothing
Set con = Nothing


Fill_Listbox


End If


End Sub

And function Fill_Listbox:

Function Fill_Listbox()


Dim mytel As Integer
Dim naam As String


Columns("A:A").Select
ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Temp").Sort
.SetRange Range("A1:A5000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


mytel = 1


Do While Range("A" & mytel) <> ""
If Range("A" & mytel) <> naam Then 'because some of the items can be the same, and i dont want every unique item in the listbox
naam = Range("A" & mytel)
With ListBox1
.AddItem naam
End With
End If
mytel = mytel + 1
Loop


End Function

 

[Andrew Poulsom]

You don't need to Select a Worksheet or a Range to use their Properties/Methods. For example:

Sheets("Temp").Visible = True 'I use a temporary sheet because
Sheets("Temp").Select
Range("A1:Z5000").ClearContents

can be written thus:

Sheets("Temp").Range("A1:Z5000").ClearContents

 

[robert_645]

Thanks for your reaction, I don't select the sheet, which solves my problem.
Another question about the same code:
Now I fill the sheet from column "KWR1", but I would like to make that name variable, because there is also a column "KWR2" which I would like to read with the help of a do loop (see the code already)

[COLOR=#333333]SQL_Login.Login_SQL 'function to make a SQL connection[/COLOR]




[COLOR=#333333]Do While mytel < 2 'I have 2 columns, named KWR1 and KWR2[/COLOR]
[COLOR=#333333]kolom = "KWR" & mytel[/COLOR]


[COLOR=#333333]rc.Open "SELECT [" & kolom & "] FROM " & jaar & " WHERE [" & kolom & "] != ''", con[/COLOR]
[COLOR=#333333]rc.MoveFirst[/COLOR]

[COLOR=#333333]Do[/COLOR]
[COLOR=#333333]Range("A" & mytel2) = rc![KWR1]  '<-- This should be [/COLOR][COLOR=#333333][" & kolom & "][/COLOR]
[COLOR=#333333]mytel2 = mytel2 + 1[/COLOR]
[COLOR=#333333]rc.MoveNext[/COLOR]
[COLOR=#333333]Loop Until rc.EOF[/COLOR]
[COLOR=#333333]mytel = mytel + 1[/COLOR]
[COLOR=#333333]rc.Close[/COLOR]


[COLOR=#333333]Loop[/COLOR]


[COLOR=#333333]con.Close[/COLOR]
[COLOR=#333333]Set rc = Nothing[/COLOR]
[COLOR=#333333]Set con = Nothing[/COLOR]

 

[Andrew Poulsom]

I don't know how to do that, sorry. Concatenation requires a string, which you don't have there. There are other ways to populate a range with the result of a query eg a QueryTable.