Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Slider Control

When using the slider control, the values of the slider show above the control. If the slider is moved back and forth quickly, there is a white space aboce the control where the previous value(s) were shown. Is there a way to avoid this?


Post your answer or comment

comments powered by Disqus
Hi
Thank you for looking, I hope you can help I am trying to use the
activex slider control in the same way as you can use the spin button. I have spin button with the following code which works fine, but would prefer a slider bar, if I can!

	VB:
	
Me.SpinButton1.Min = Me.Range("y2").Value 
Me.SpinButton1.Max = Me.Range("x16").Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Many thanks
Chris

Hi

I need a "3 side" slider mechanism for a Gantt Chart. I have not been able to attach an example, but you should be able to view video clip of the requirement at:

http://www.perceptiondynamics.info/e...ing_slider.mp4

Basically, you can move either the start, finish or the body of the slider.

A while back a programmer produced the slider in the video for me. The problem was that it was an ActiveX contol that had to be registered on the machine using it. It became so flaky and non-transportable that I gave up trying to use it.

The principle of the control in the video is fine. In theory, something similar could be created by using three thin slider controls, one above the other, on a user form. In practice, this would be too bulky and inflexible because it neeeds to fit in a single row on a spreadsheet.

I would have thought it was a fairly general requirement. However, I cannot find anything suitable. The main thing is that it needs to be as portable as possible and work on different machines and different versions of Excel

I would really appreciate any ideas - because I am really stuck on this one

Thanks

I am programming a gantt chart in excel. I have downloaded the 'vertex 42' template but I need to change it slightly. You can see the template Im talking about by downloading the chart from

http://www.vertex42.com/ExcelTemplat...chart.html?xls

and I have also attached it here

I have made a new sheet but what I want to include is the slider control shown in L8

Any help would be gratefully received!

I'm using a listbox to select states and cities. [I'm not using the ActiveX
listbox because it needs to work in both PC and Mac environments.] Selecting
a state in one listbox populates the second (city) listbox, which has a
different size depending on the state. My problem is this:

If the last city I selected was Yuma Arizona, when I switch to Arkansas I
want the (alphabetized) city first up to be Alma -- which I can control by
putting a 1 in the cell link. But the slider on the right of the listbox is
halfway down the listbox instead of sitting up at the top.

How can I force the slider to the top?

We are using the Microsoft slider Control, Version 6.0 as a range selector for a user interface for an excel workbook. Unfortunately, we are experiencing some problems. The slider is not re-sizing properly in Excel 2007 or 2010. We are able to change its width, but not its height. When we decrease the height, the proportions of the slider do not change, but the bottom edge of the slider moves upwards, typically cutting off part of the slider.

The slider is currently set to move and size with cells. When we increase the height of the cell that the slider is in, only the slider’s bottom edge grows larger, leaving the main part unchanged.

The height maxes out at 33.75 when the slider is not set within a cell. It seems as though changing the height of the slider can only cover up part of the slider.

Thank you for your help

cross-posted here http://www.mrexcel.com/forum/showthread.php?t=563690

Hi Folks,

I’m relatively new to Excel 2000 programming. When I add frames to a
Worksheet with embedded controls (to give the sheet a User Interface look),
the controls are visible in design mode, but disappear (behind the frame?)
when I exit design mode. I have played with every frame/control property and
also tried ordering the shapes, but not having any luck. The slider control
however, is the only one that is visible on top of the frame when not in
design mode – check boxes, combo boxes, and labels seem to disappear.

Is there a way to embed frames into a worksheet that react the same as those
within a Userform?

Thanks,
Andrew

The following is an example sub to illustrate my question. On a UserForm, I have several slider controls (sldrC, sldrK, sldrL, sldrM, sldrN) that change associated named ranges in a worksheet (e.g. perC, perK, etc) or read specific cell addresses (e.g. E5). They also change the values of their associated labels on the userform (e.g. lblCperc, lblKperc, etc). The basic code for all five sliders is identical except for differences in ranges, labels. Rather than re-writing the code five times, is there a way I can call one sub and specify the correct ranges and labels depending on which slider control I'm calling it from? The sub below is for sldrC.

	VB:
	
 simTransC() 
    Dim stockShip, lastVal, maxAllow As Long 
    Dim strMsg As String 
     
    lastVal = frmTransPM.sldrC.Value 
     
     'Check if shipments exceed inventory level
    With Sheets("Main") 
        stockShip = .Range("percC").Value + .Range("percN").Value + _ 
        .Range("percM").Value + .Range("percL").Value + .Range("percK").Value 
        maxAllow = (stockShip - .Range("percC").Value) * 100 
    End With 
     
    If stockShip * 100 > 100 Then 
        strMsg = "Insufficient inventory in mill warehouse to " 
        strMsg = strMsg & "ship " & frmTransPM.sldrC.Value - 1 & "%" 
        strMsg = strMsg & vbNewLine & "of its stock to sheeters." 
        strMsg = strMsg & vbNewLine & vbNewLine 
        strMsg = strMsg & "Maximum allowable is " & maxAllow & "%." 
        MsgBox strMsg, vbOKOnly + vbExclamation, "Inventory violation" 
        frmTransPM.sldrC.Value = maxAllow 
        Sheets("Main").Range("percC").Value = frmTransPM.sldrC.Value _ 
        / 100 
        frmTransPM.lblCperc.Caption = "(" & frmTransPM.sldrC.Value & "%)" 
        frmTransPM.lblCTons.Caption = frmTransPM.sldrC.Value / 100 * _ 
        Sheets("Main").Range("E5").Value & " tons" 
        Exit Sub 
    End If 
     
    With Sheets("Main") 
        .Range("percC").Value = frmTransPM.sldrC.Value / 100 
        frmTransPM.lblCperc.Caption = "(" & frmTransPM.sldrC.Value & "%)" 
        frmTransPM.lblCTons.Caption = frmTransPM.sldrC.Value / 100 * .Range("E5").Value & " tons" 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I’m trying to create a decision making tool that uses a slider control to
change a cost per unit field. I used a scroll bar from the Forms toolbar.
However, this control is not able to increment decimal values. Because I am
using currency, I need .01 detail, however, any decimal value in the
‘Incremental change’ field in the ‘Control’ tab of the ‘format control’ for
the slider is replaced with ‘0’. I’ve currently linked the control to
another cell and the relevant cell has a ‘/100’ formula, but now my users
can’t type a value directly. I’d appreciate any help. Thanks.

Does anyone know how to stop sliders going out of control. Specifically,
when we use sliders, many times we find that when you click on the "page" or
"increment" area of the slider, the slider will continue to change the value
of the link cell even after the click is off. This is very annoying. Does
anyone understand this? Does anyone have a work around?

Hello- When using activeX control sliders in Excel 2003, the slider is changing shape... basically growing each time I use it as a slider (not in design mode). It become HUGE within a couple slides. Why is this happening and how do I prevent it from happening?

Thanks for the help!

Hello all - this is my first time in the forum.

I am looking for a non-vba method of controling the "max" y-value on a chart where the user can use a scroll bar/slider to adjust the y-axis. This way they can use a smaller increment to enhance the view of items that are very low on the scale.

Can a scroll-bar be linked to a chart??

Hope someone has solved this already.

I have a Form Control slider on a worksheet with the range 50 - 200.

I need it to change "increments" halfway through the range.
(e.g. First four increments (75, 100, 125, 150) are 25. But at 150 need to change the increments to 10, finishing at 200.)

Appreciate any help on this, either through VBA or formulae.

Ochimus

I use sliders often, and to make the job easier I keep a library of various ones to cut and paste. This requires the task of fomatting the control to a new cell link. I attempted to automate it with the macro below:

Sub Slider()
Dim celladd As String

For i = 1 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Name = i
ActiveSheet.Shapes(i).Select

'///// Prompt to select the cell while leaving slider selected

Set mc = ActiveCell
celladd = mc.Address()

ActiveSheet.Shapes(i).Select
With Selection.LinkedCell = celladd
End With
Next i

End Sub

It seemed straight forward, but once the slider is selected in the macro, I'd like to prompt for the user to click the cell it will be addressed to. I've tried input and message box, and wait, but when the program encounters it, the slider looses it's visual indication that it is selected. It works if single stepping through the code, but not when the macro is run. I need something to put in the Remmed line of code.

Any ideas?

I'm still working with Excel 2000 , I've inserted 2 scroll bars and a toggle button on my spreadsheet. They work according to how I want except when I password protect the sheet. When the spreadsheet is protected, the button and sliders can't be accessed. I've unlocked all three, in the properties dialogue box, but still can't be accessed when sheet is protected.

Do I need to create some type of VBA command so that access to these controls is possible when I password protect the spreadsheet? Thnx...

Hello,

I have a group of values (example below) in a model. I would like to use a slider to increase and decrease the values. I would like the middle of the slider to be set at 0 and then be able to move the slider to the right increase by 1 up to say 10 and be able to move the slider to the left to decrease by 1 up to 10. Moving the slider would have the same increase/decrease on each of the values below. One problem I am seeing is that excel prohibits me from entering a negative number in the "format control". Are there any workarounds? If not, how can I do this for only increases? Thanks in advance for your help.
A
1 9
2 8
3 12
4 7
5 15
6 9
7 17
8 21
9 5
10 8

When the slider is clicked on and moved back and forth, different input data
is selected for graphing a function. For example, for y = mx + b, moving the
slider inputs different values for x (and/or b) which causes a graph of the
function to change in real time. It is a great way to see how a variable
affects the value of a function.

Is it possible to set up a slider to control plus and minus 50% from a value by using formulas and/or macros? An example would be a base value of 0.2 that can be varied from 0.1 to 0.3; or, a base value of 300 that can be varied from 150 to 450. Actually the base value could be any number. I just need to be able to change it by 50% in either direction.

Hi, I created my first userform in Excel and was happy to discover the Control Source Property. Every field in my form links to a particular cell on my worksheet, and I like how changes made to one automatically update on the other. However, I realized the close button on my userform basically acts the same as a Submit button -- the changes save anyway instead of canceling. Does this mean Control Source is not the way to go? I simply want a form that updates the worksheet once the Submit button is clicked, but also gives users the option to cancel out of the form (via clicking the window close "X" button) without submitting any changes. I think I tried that before, however, without using Control Source, is there still a way for the form to be populated automatically if opened again? I noticed before that every time the form was opened, the fields were blank, so that if someone needed to change/update one field & clicked Submit, all the rest of the fields on the worksheet would go back to being blank as well. Sorry if this question is long-winded, but basically, what coding would I need for the form to open & re-populate w/ previously submitted values (or whatever values on currently on the worksheet) every time, instead of being blank? And what's the best way to code a Submit button so I don't have to use Control Source any longer? Please dumb down your answers (i.e. exactly where to put the code) as I'm still a beginner. Thanks for your time!!!

Hi,

I am working on userform spreadsheet 11.0 control. I want to do on thing with this spreadsheet like i did in the normal excel worksheet, but i am not able to do it. i want to create a macro to undo between a range given. for example i do this in normal worksheet.

eg: Private Sub Worksheet_Change(ByVal Target As Range)If ActiveCell.Row 2 Then: Else Exit Sub
If ActiveSheet.Cells(2, ActiveCell.Column) ActiveSheet.Cells(1, 250) Then: Else Exit Sub
If Not Intersect(Target, Columns("E:P")) Is Nothing Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
End Sub

Now, Here we have worksheet change event according to this if any change happens into the worksheet. this code will undo that change for that particular range then and there. Now i want to apply this similar code into spreadsheet control, but there is no spreadsheet change event is there. Please help me on this.

I currently change the backcolor of a control(textbox and comboboxes) on a userform on entry or exit, yellow when active and white when not, is there a better way to do this? There is a multi page that does not need to change color that all the other controls are in. The controls on the multipage are in frames as well. John

My code is like this

	VB:
	
textbox1.backcolor= rgb(255,255,153) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I tried to make a function but failed miserably!!

I am new to VBA and thought I could accomplish this feat through various forum threads. So far so good but I'm stuck now. As the title suggest, my objective is to build these security features into a very large workbook/database:When workbook is opened all worksheets are automatically protected and hidden (xlVeryHidden);then a UserForm with a Combobox, Inputbox and CommandButtons (Ok+Cancel) is displayed;Users then select from the Combobox list (8 items) and enter a password (text string) into the Inputbox and click Ok (each listed item has its own password recorded in a hidden worksheet range);If combination of listed item and password is validated a the relevant worksheets are made visible (non relevant worksheets remain "veryHidden") and;the UserForm is either hidden/minimised(ribbon controls/right click menu etc), however should be able to call the UserForm from the current view with ease.If combination is invalid, an error message is displayed and requested to try again. Cancel button should close workbook, and top-right 'X' (exit button) should close workbook as well.I have managed, through various threads, to code steps 1 and 2, and others for the Cancel and 'X' buttons. I have also found a few threads to help with coding the inputbox for passwords but have not progressed until the combobox issue is resolved.

Where I have been struggling the most is how to assign the names in the combobox list to their respective worksheets so that when selected only those worksheets are visible.
see the relevant code below.
Can someone please, please, help.

Code for ThisWorkbook object:

	VB:
	
 Workbook_Open() 
    Dim sht As Worksheet 
     '
    Application.EnableEvents = True 
    Application.ScreenUpdating = False 
    ActiveWorkbook.Unprotect wbPassword 
    For Each sht In ActiveWorkbook.Worksheets 
        With sht 
            .Protect Password:=wsPassword, UserInterfaceOnly:=True 
            .EnableOutlining = True 
            .Visible = xlSheetVeryHidden 
        End With 
    Next sht 
    ActiveWorkbook.Protect wbPassword 
     '
    frmCtrAccess.Show 
     '
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
'
Code for Userform object:

	VB:
	
Private Sub UserForm_Initialize() 
    With Me.ComboBox1 
        .Clear 
        .AddItem "Fish" 
        .AddItem "Amphibian" 
        .AddItem "Bird" 
        .AddItem "Insect" 
        .AddItem "Mammal" 
        .AddItem "Reptile" 
        .AddItem "Animals" 
        .AddItem "Master" 
    End With 
End Sub 
 '
 'calls the ChangeCombo procedure when item is selected from combobox list
Private Sub ComboBox1_Change() 
    ChangeCombo 
End Sub 
 '
 'closes the workbook when the top-right 'X' on UserForm is clicked
Private Sub UserForm_Terminate() 
    ThisWorkbook.Close (savechanges = False) 
End Sub 
 '
 'closes the workbook when the commandbutton 'Cancel' is clicked
Private Sub CommandButtonCancel_Click() 
    ThisWorkbook.Close (savechanges = False) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Code for Module:

	VB:
	
 ChangeCombo() 
    Dim wsFish(3) As Variant 
    Dim wsAmphibian As Variant 
    Dim wsBird As Variant 
    Dim wsInsect As Variant 
    Dim wsMammal As Variant 
    Dim wsReptile As Variant 
    Dim wsAnimals As Variant 
    Dim wsMaster As Variant 
    Dim strComboValue As String 
     '
    wsFish(3) = Sheets(Array("Freshwater Fish", "Saltwater Fish", "Sharks_Rays", "Sea Creatures")) 
     '
    wsAmphibian(1) = Sheets(Array("Frogs_Toads", "Salamanders")) 
     '
    wsBird(5) = Sheets(Array("Backyard Birds", "Ground Birds", "Birds of Prey", "Owls", "Seabirds", "Woodpeckers")) 
     '
    wsInsect(8) = Sheets(Array("Arachnids", "Beetles", "Butterflies", "Centipedes", "Bugs", "Flies", "Grasshoppers", 
"Dragonflies", "Social Insects")) 
     '
    wsMammal(5) = Sheets(Array("Carnivores", "Hoofed Mammals", "Marine Mammals", "Primates", "Rabbits", "Rodents")) 
     '
    wsReptile(3) = Sheets(Array("Crocodilians", "Lizards", "Snakes", "Turtles")) 
     '
    wsAnimals(30) = Sheets(Array("Freshwater Fish", "Saltwater Fish", "Sharks_Rays", "Sea Creatures", "Frogs_Toads",
"Salamanders", "Backyard Birds", "Ground Birds", _ 
    "Birds of Prey", "Owls", "Seabirds", "Woodpeckers", "Grasshoppers", "Dragonflies", "Social Insects", "Carnivores",
"Hoofed Mammals", "Marine Mammals", _ 
    "Primates", "Rabbits", "Rodents", "Crocodilians", "Lizards", "Snakes", "Turtles")) 
     '
    wsMaster(31) = Sheets(Array("Settings", "Freshwater Fish", "Saltwater Fish", "Sharks_Rays", "Sea Creatures",
"Frogs_Toads", "Salamanders", "Backyard Birds", _ 
    "Ground Birds", "Birds of Prey", "Owls", "Seabirds", "Woodpeckers", "Arachnids", "Beetles", "Butterflies", "Centipedes",
"Bugs", "Flies", "Grasshoppers", _ 
    "Dragonflies", "Social Insects", "Carnivores", "Hoofed Mammals", "Marine Mammals", "Primates", "Rabbits", "Rodents",
"Crocodilians", "Lizards", "Snakes", _ 
    "Turtles")) 
     '
    Let strComboValue = frmCtrAccess.ComboBox1.Value 
     '
    Select Case strComboValue 
    Case Is = "Fish" 
        wsFish.Visible = xlSheetVisible 
        wsFish.Select 
         '
    Case Is = "Amphibian" 
        wsAmphibian.Visible = xlSheetVisible 
        wsAmphibian.Select 
         '
    Case Is = "Bird" 
        wsBird.Visible = xlSheetVisible 
        wsBird.Select 
         '
    Case Is = "Insect" 
        wsInsect.Visible = xlSheetVisible 
        wsInsect.Select 
         '
    Case Is = "Mammal" 
        wsMammal.Visible = xlSheetVisible 
        wsMammal.Select 
         '
    Case Is = "Reptile" 
        wsReptile.Visible = xlSheetVisible 
        wsReptile.Select 
         '
    Case Is = "Animals" 
        wsAnimals.Visible = xlSheetVisible 
        wsAnimals.Select 
         '
    Case Is = "Master" 
        wsMaster.Visible = xlSheetVisible 
        wsMaster.Select 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have an excell document that has several Image active x controls on it. These controls link to other spreadsheets and pull them up once they are clicked.

What I need is some code that will save and close the excel spread sheet once they click on any of the image active x controls.

So,

They open up my spreadsheet, click on a image control, and the document saves and closes right after without prompting the user. and the spreadsheet they just open remains open.

Can someone write up some code for me??

Thanks for your help. If you need some clarification let me know.

Thanks

Hi,

Hopefully someone can assist...I am getting desperate now.

My spreadsheet is setup with a Dashboard, Data, Calculations, and Lists worksheets. In the Dashboard worksheet I managed to create a format control using a list of years in the Lists worksheet, by way of Name Manager. The Calculations worksheet has a cell called Year. There are many places where I have Year in the Calculations worksheet because of adjacency to other calculations I have using DCount, DSUM, etc. The cells below cell text Year have cell formulas of =Year. This is what my workbook does at a very high level:

1) When I select 2009, 2010, 2011, etc. in the Dashboard worksheet format control, the =Year cells in the Calculations worksheet changes to all 2009 depending on how many I need and have cells configured to use formula =Year.
2) I am able to use this list and name manager for about 12 other names created and to dynamically change what will be calculated and in return, what will be displayed on my Dashboard worksheet.

What I want to do and am having a hard time figuring out, which will lead to my ultimate DAVERAGE or SUMPRODUCT solution is:

How can I select 2009 from my format control under the Dashboard worksheet and then display in the Calculation worksheet each month of the year and year together for the year I selected in my dashboard worksheet format control? It sounds like a calendar type formula and something else.

Therefore, when I select 2009 in the Dashboard worksheet format control that is visible for all years in a box (not a dropdown, combo, radio button, check box or slider), I want to populate 12 cells in the Month column so it looks like:

The formula for Year cells is =Year

In Calculations worksheet I want the following to occur when selecting a Year from the Dashboard worksheet format control box...

Year Month 2009 Jan-09 2009 Feb-09 2009 Mar-09 . . . 2009 Dec-09

When I select 2010 in the format control box in the Dashboard worksheet, the Month in the Calculations worksheet changes to Jan-10, Feb-10, March-10, and etc.

The root of my crux of what I am ultimately trying to achieve is getting the average per month of data in my Data worksheet for a selected year. The Daverage formula is not averaging correctly unless I use criteria such as ="1/1/09" for each month. However, this does not create that dynamic and automatic update I am looking for when using the format control for Year in the Dashboard worksheet, which is imperative to keeping things streamlined and not having to reference ranges of cells from Jan-09 all the way to infinity.

I would love for someone to take a crack at this as it's the second to last remaining element, other than getting accurate averages per month for a selected year using DAVERAGE or SUMPRODUCT.

If you need more information let me know and I will try to respond ASAP. However, the data I have is confidential so please bear with me as I try to expunge from various worksheets and formulas.

Best and Thanks in advance,

Tom

Hi,

I'm running a database in Excel where each entry has 4 specific photo files associated.

For one step of a project I'm working on, I have photo files that need to be resized to the Document Large (1024xSomethingIDon'tRemember) in Microsoft Office Picture Manager.

After hassling with resizing in Excel directly via a bunch of work-arounds, I gave up on that plan. I now have the macro take the 4 files, copy them into a temporary file, open the temporary file so that the user can manually open them in Picture Manager and resize them, then the macro continues with further processing of the images and deletes the temp files when finished.

Is there any way I can automate the resizing process by controlling the Picture Manager program in Excel VBA? I'm not even sure where to begin with this, and I can't seem to find any help online...

Thanks,
Andy


No luck finding an answer? You could always try Google.