Userform modeless and screenupdate = false in excel 2013

robert_645

New Member
Joined
Nov 28, 2014
Messages
17
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Why would you need ScreenUpdating = False when scrolling a worksheet? That command only applies to VBA code.
 

robert_645

New Member
Joined
Nov 28, 2014
Messages
17
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
 

robert_645

New Member
Joined
Nov 28, 2014
Messages
17
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You don't need to Select a Worksheet or a Range to use their Properties/Methods. For example:

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

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

robert_645

New Member
Joined
Nov 28, 2014
Messages
17
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)

Code:
[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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,082,141
Messages
5,363,368
Members
400,731
Latest member
Jackserver

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top