• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to hide a combobox...

andrewp7

New Member
Hi all,


I have a Combobox with percent complete in the menu (ie 0%, 20%...100%).


When the user selects 100% complete I would like to hide the Combobox.


Any ideas on how to do this?


Many thanks


AP


Ps Excel 2007 & a basic understanding of VB!
 
Hi Andrew ,


A Combobox has a property called Visible ; normally this is TRUE , which is why the Combobox is displayed ; if this is changed to FALSE :


IF Combobox88.Value = 100 then

Combobox88.Visible = FALSE

ENDIF


it should do the job.


But why would you want this ? After all , you need the Combobox the next time , to allow the user to select a different value !


Narayan
 
Thanks Narayan,


How would you expand that code to say if value is not equal to 100 then combobox visible? When I include your code I get an error when selecting any value other than 100%.


Also when you used '100' will Excel interpret that as '100%'? or should we use '1'?
 
Hi Andrew ,


The visibility of the Combobox can be made TRUE again , but if this is dependent on the user entering a value other than 100 , which in turn depends on the Combobox being visible , how will it happen ?


Whether 100 equals 100 or 1 equals 100 depends on how you are entering the other values ; how are you entering values of 10 % or 25 % or any other value less than 100 ? Are you displaying the values 0.10 , 0.25 or are you displaying 10 , 25 and so on ?


Finally , a value of 1 can be made to show 100 % if the cell is formatted as Percentage ; but how will you be using this value in your calculations ? Will it be as values between 0 and 1 , or will it be as values between 0 and 100 ?


Narayan
 
Some context: I'm building a linear sequence chart (modified Gantt chart) which visualises the process plan for a longwall mining operation in Central Queensland in Australia. This isn't a chart in Excel terms but rather a conditionally formatted worksheet with tasks down the Y-axis and distance across the X-axis. The finished chart is going to sit on a 70" SMART Tech touchscreen display (very cool toy) and will be updated by each crew as they finish their shift giving the next shift a snapshot of progress to date.


Functionality: What started as basic data validation drop down lists has morphed into Active X comboboxes due to big miners with big fingers struggling to make selections on the screen. In this instance the combobox has the following options available: 0%, 20%, 40%, 60%, 80%, 100%.


For each task miners update their progress by shift, when it reaches 100% conditional formating turns cells to green & I would like the combobox to be 'hidden'.


Your questioning did spark some thoughts in my mind (thank you), rather than hiding the whole combobox when '100%' is selected from the drop down menu can I:


1) Change the 'ShowDropDownButtonWhen' from 'Always' to 'Focus'?


2) Change the 'ForeColor' to match the 'BackColor'


The objective here is to keep the worksheet 'clean' so as progress is made there are less & less comboboxes viewable on the chart. Once the entire process is complete the chart can then be reset back to '0%' for each task. The only caveat I can think of is if '100%' is unintentionally selected then the user needs to be able to go back into the drop down menu & select the correct value.


Look forward to hearing your thoughts.
 
Hi Andrew ,


I think the first option ( Change the 'ShowDropDownButtonWhen' from 'Always' to 'Focus' ; this should be done only when the value of 100 % has been entered ) will be good. It would be nice if , once 100 % has been achieved , the font is made bigger , so that it stands out.


Narayan
 
At present my VB code for the comboboxes is below, what do I need to add to enable the 'Always' to 'Focus' and change the 'ForeColor' to match the 'BackColor'?


There are approximately 100 comboboxes on the worksheet so my preference is to make them as unobtrusive as possible, but I understand where you're coming from with making them stand out.


-----------


Private Sub ComboBox10_Change()


ComboBox10 = Format(ComboBox10, "0.0%")

Range("J16") = ComboBox10.Value


End Sub
 
Hi Andrew ,


This should do the job.


Private Sub ComboBox10_Change()

Application.EnableEvents = False

ComboBox10 = Format(ComboBox10, "0.0%")

Range("J16") = ComboBox10.Value

If ComboBox1.Value = "100.0%" Then

ComboBox1.ShowDropButtonWhen = fmShowDropButtonWhenNever

End If

Application.EnableEvents = True

End Sub


fmShowDropButtonWhenFocus does not seem to work.


You can put back this property to fmShowDropButtonWhenAlways , when you do a reset.


Instead of changing the foreground colour to the background colour , you can play around with the SpecialEffect property and see which style looks good.


Narayan
 
Thanks Narayan, hmmm I'm going to stop pursuing sequence chart Nirvana and just leave the comboboxes showing - Pareto's rule!


My last task is the reset functionality - I just tried using the macro recorder to record selecting a combobox & clearing the contents but it didn't register.


What code do I need to select the comboboxes & clear the contents?
 
Hi Andrew ,


The following should do it.


Public Sub Combobox_Reset()

If Range("Q12") Then

Set List_of_objects = Me.OLEObjects

For Each control_object In List_of_objects

If control_object.Name Like "ComboBox" & "*" Then

control_object.Object.Value = ""

End If

Next

End If

End Sub


I have used the cell Q12 to hold a TRUE or FALSE value ; if it is TRUE , then all the comboboxes will be cleared.


If you want , you can have a Command Button , which when clicked , will execute the above procedure.


If you wish , you can also include an IF statement within the procedure , to ensure that only those comboboxes which are at 100 % will be reset.


Narayan
 
Thanks Narayan,


Back from a business trip & continuing this!


hmmm I get 'Compile error: Invalid use of Me keyword'...any ideas?


With regards to functionality, I have a button (just a shape linked to a macro) on the chart 'Reset Chart', when this is clicked I'd like an 'ok/cancel' box to appear to confirm the running of the macro - is that involved to include in the code? I don't want people bumping the button on the screens accidently & clearing the chart.


Many thanks


Andrew
 
Hi Andrew ,


This error is appearing because your procedure is in a Module ; if you move it to the relevant sheet ( under Microsoft Excel Objects ) you should not get this error.


Alternatively , you can replace the Me keyword with the following :


ThisWorkbook.Sheets("Sheet1")


where instead of Sheet1 , put the worksheet name of the sheet where your combobox is present.


Regarding the confirmation step before resetting , you can include the following lines in your existing macro :


user_input = MsgBox("Are you sure you wish to RESET all charts ? (Y/N) : ", vbYesNo)

If user_input = vbYes Then

'

'Your existing code should appear here

'

End If


Narayan
 
Narayan,


Back from holidays and have a loose end if you could help please?


In the Yes/No box above I need a line of code to handle the action if a user selects 'No' ie close the box & do nothing.


Could you assist please?


Many thanks


Andrew
 
Hi Andrew ,


Can you mention what happens now ?


From the code , you are checking for the user's response = Yes ; so if the user has responded by pressing "No" , nothing should happen.


Can you clarify ?


Narayan
 
Hi Andrew ,


Our earlier discussion in this matter was :


Regarding the confirmation step before resetting , you can include the following lines in your existing macro :


user_input = MsgBox("Are you sure you wish to RESET all charts ? (Y/N) : ", vbYesNo)

If user_input = vbYes Then

'

'Your existing code should appear here

'

End If


So , logically , the clearing of all the comboboxes should appear in between the :


If user_input = vbYes Then


and :


End If


If the user_input is not vbYes , then nothing should happen.


Can you copy + paste your complete code here ?


Narayan
 
Public Sub Combobox_Reset()


user_input = MsgBox("Are you sure you wish to RESET the sheet ? (Y/N) : ", vbYesNo)


If user_input = vbYes Then

'

Set List_of_objects = Me.OLEObjects

For Each control_object In List_of_objects

If control_object.Name Like "ComboBox" & "*" Then

control_object.Object.Value = ""

End If

Next

End If


Range("T4:T17").Select

Range("T19:T32").Select

Range("F37:U37").Select

Range("F40:I40").Select

Range("Z37:AO37").Select

Range("Z40:AC40").Select

Range("AN19:AN32").Select

Range("AN4:AN17").Select

Range("BH4:BH17").Select

Range("BH19:BI32").Select

Range("AT37:BI37").Select

Range("AT40:AW40").Select

Range("J1").Select

Range("M3").Select

Range("AG3").Select

Range("BA3").Select

Selection.ClearContents

Range("J2").Select


End Sub
 
Hi Andrew ,


There are two problems in the code which need to be corrected :


1. You are doing two things within your code ; one is to clear the comboboxes , and the second is to clear the contents of the various ranges.


The clearing of the comboboxes is being done correctly , within the "IF user_input = vbYes THEN ... END IF" block.


However , clearing the contents of the various ranges , is being done outside of the block ; so whether the user selects Yes or No , the clearing of the ranges will always be done. Is this what you want to do ? If it is not , then the second End If in your code needs to be moved to just before the End Sub.


2. You are selecting several ranges one after another. However , clearing of the range contents is being done using a Selection.ClearContents method ; here the selection range on which this operates is just the last range which has been selected i.e. BA3.


To actually select multiple ranges , you need to use the Application.Union method as follows :


Application.Union(Range("T4:T17"), Range("T19:T32"), Range("F37:U37"), Range("F40:I40")).Select


will select the four non-contiguous ranges T4:T17 , T19:T32 , F37:U37 and F40:I40.


Narayan
 
Back
Top