Show / Hide UserForms Wizard
Dec 17, 2007
I have created several userforms within my excel spreadsheet and i want to be able to click a command button on one form which will then hide the current form and show the next form. In previous spreadsheets i have used the following:
Private Sub CommandButton1_Click()
Form1.Hide
Form2.Show
End Sub
However, i am having trouble with this spreadsheet as when i am typing my code after the Form1. the hide command (and show) does not appear in the little pop scroll bar that predicts what command you plan to use. (If i type it regardless then it doesnt execute)
What is even stranger is that if i write a sub routine in a module such as:
Sub Test_Form()
Form1.Hide
Form2.Show
End Sub
And then call it:
Private Sub CommandButton1_Click()
Call Test_Form
End Sub
View 5 Replies
ADVERTISEMENT
Sep 18, 2006
I have a commandbutton that when clicked, it would make monthview1 visible. I can do this via the following
Private Sub CommandButton3_Click()
MonthView1.Visible = True
MonthView1.Value = Date
End Sub
However, I do not know what code to add so that when the same commandbutton is clicked, the monthview object would hide.
View 2 Replies
View Related
Sep 6, 2007
I'm just beginning to work with userforms and have created a couple of forms for workbook navigation. The Menu form is set to appear only on the menu worksheet and the Navbar form is set to show on several database sheets. Both forms are set th hide when the workbook is deactivated.
Private Sub Workbook_Deactivate()
Menufrm.Hide
Navbar.Hide
End Sub
The problem is that when I return to the workbook the forms do not unhide. Using the workbook_activate event causes both forms to show simultaneously. I also have the worksheet_activate event set to show the applicable form.
Private Sub Worksheet_Activate()
Application. ScreenUpdating = False
ActiveWindow.DisplayGridlines = False
Navbar.Show
Navbar.Left = Range("B3").Left
Navbar.Top = Range("B3").Top
Application.ScreenUpdating = True
End Sub
Upon returning to this workbook, I only want the appropriate form to show relative to the worksheet that is active. That is, if I was on sheet2 when I switched to another workbook, sheet2 would be active when I return to the workbook and should show the Navbar form. The menu form should show on sheet1, and the navbar form on sheets 2-5. How do I get the correct form to show when I re-activate the workbook?
View 4 Replies
View Related
Feb 26, 2008
i have a form that creates a pop-up (another form) and i want that pop-up to remain on top of the original form until the pop-up's "submit" or "cancel" button is clicked - the original form should be inaccessible while the pop-up is there.
is it possible to do this in VBA?
View 6 Replies
View Related
Apr 27, 2008
I'm writing an application for myself, running on Windows Vista & Office 2003, I have no intention, as things stand, of this being used on any other PCs or by anybody using an older version of Windows or Office. I am a Mainframe programmer so my logical way of thinking is not always in line with the best way of using VBA, but I try my best & seem to be getting by, but I have a problem & question as follows:
I "SHOW" a UserForm with an option to Add or Delete. I choose Delete & a new UserForm is opened, populated with data from an Access database, I choose my option from a DropDown box, click OK & I then want the data to be deleted and to return to the first UserForm. I "UNLOAD" the latter UserForm and then try to "SHOW" the original one, but get an error message saying "Form already displayed; can't show modally" ... the first port of call:
You can't use the Show method to display a visible form as modal. This error has the following cause and solution: You tried to use Show, with the style argument set to 1 – vbModal, on an already visible form.........
View 4 Replies
View Related
Apr 2, 2014
I am close to finishing making up an Excel tool that takes data input from the user, does some analysis, accepts further input then prepares a report. My coding skills are not what you would call high level, so my code could probably be more efficient, but it works (well, except for this issue).
The tool opens a main multipage userform for data entry, then allows the user to view the data in the spreadsheet tables with the ability to call separate userforms to modify, delete or add records. The user then calls another userform to do some calculations and enter the appropriate results from a separate piece of software, then presses a button to prepare a report. All userforms are called from buttons in a separate userform (I'm not proficient enough and haven't had enough time to create a new ribbon in excel). At various times, messageboxes are used to convey information or prompt the user to do something - these are all simple ones with either only OK button, or OK and cancel buttons.
My problem is that when userforms are called, they sometimes appear but are totally blank (white) - see image.
blank_form.jpg
Clicking anywhere on the screen or pressing any key will bring them up properly and they seem to function normally thereafter.
In addition, the messageboxes sometimes do not appear - pressing any key will make them do so. Other than this they function normally.
The frustrating thing is that these problems occur inconsistently. I can add or change some code and it stops happening, then after testing (entering data and using the tool) starts happening again. Sometimes it just stops happening with no apparent reason, but starts again later after fixing some code or just entering some data. Not all userforms are affected at any one time and not all messageboxes have the problem at any one time.
The problem seems to occur in those subroutines where I access or manipulate data from other sheets before showing the userform or message box (but in most instances, I need to do this manipulation in order to present the correct information in the userform).
Some thought processes I have had, and unsuccessfully tried to fix the problem with (some coming from various web forums):
I thought I may have done "Application.ScreenUpdating = False" without resetting it to true, so I commented out all the "Application.ScreenUpdating = False" statements - problem stopped for a bit then started again.
Some of the userforms were modal, and even though they were not showing were still loaded so thought that this may be impacting on other userforms/message boxes, so changed all userforms to non modal - still had the problem when I opened the tool again.
Tried using "RePaint" and "DoEvents" at various points in the code after opening a userform or message box - no change.
I have tried exporting a form and its code, removing from the tool, then re-importing it. No success.
Tried the tool on another computer and the same problem occurred.
View 10 Replies
View Related
Mar 30, 2014
get my code to work.
I have a sheet with a dropdown box in cell "J1" which is meant as a 'Show only rows containing this value'.
Column Q, from row 3 downward has a pick box which lists the same values as the "J1" drop down box, but uses code to combine whats chosen with commas. An example of what is in a cell in column Q is "SeaHawk, BlackHawk, Squirrel, MRH", where cell J1 would be either "SeaHawk" or "MRH" or something completely different.
I need to only show rows, with text in column Q containing the smae text as J1.
View 8 Replies
View Related
Mar 17, 2009
My Excel>Preferences>View>Comments is set to Comment Indicator Only.
Working from keyboard and mouse, when I click on a cell holding a comment ($B$52) , the comment appears. When I click on a different cell the comment disappears.
When I run the code
...
Range("B52").Select
End Sub
the cell is selected, but no comment appears.
When I run
...
With Range("B52")
.Select
.Comment.Visible = True
End With
End Sub
the comment remains visible even after I click off of B52.
I get the same behaviour when I use Application.Goto rather than Select.
I would like to write a routine that
1) creates a comment for cell B52
2) selects B52
3) shows the comment in B52
< VB routine ends >
4) comment disappears when user clicks on different cell.
Does this require event code?
View 9 Replies
View Related
Mar 10, 2006
I have two options in a Cell as Applicable and Not Applicable. This is in a Drop-down menu and what I want is that when I choose Applicable, it should bring up additional 5 or 6 cells for me to complete. And if I choose Not Applicable, the additional cells should not show up. The default would be Not Applicable.
View 9 Replies
View Related
Dec 29, 2006
I bet you're getting sick of the "X" questions, and I have done an exhaustive search but the search filter kills searching for (X) or 'X' or "X" .. but nothing I did find is what I am looking to do. I have any # of userforms that when you click the "X" in the top right side, it will hide the userform... once.. Is there a fix to the code below to make it do it every time its clicked?
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
If CloseMode = 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
End Sub
Like I said, I don't want to disable it, I tried that off of one of the posts I found and thats just plain annoying, I just want it to hide the one userform and show the other. This will do it once and then you can click it all you want and it wont do anything.. see the annoying trait above.. this lasts until you exit the program.. so the X button can be hit repeatedly though out the day. The userform 15 is visible, userform 11 isn't shown, IE not behind userform 15.
View 5 Replies
View Related
Jun 1, 2007
how to creat a userform with a button (1) to show anouther userform with a button (2). you click the button on 1 and it shows 2. then when you click the button on 2 and it shows 1. I'm having a problem with the second part.
View 5 Replies
View Related
Jul 24, 2007
Is there a method of formatting specific cells to show zeros? Or indeed I could turn on show zeros in options and then hide the ones I don't want. Any tips / advice on how to selectively display (or hide) zeros appreciated.
View 3 Replies
View Related
Jan 10, 2014
I have a some data in 1st column in which most of the data has decimals. For ex. 209.8, 224.4 like this. I have converted it by roundup function and get the exact result. I want to hide the decimals means not show decimals. Only the result will show like. 210, 224 etc. For your reference I have attached the sheet. I have manually typed this in the C column result area. That I need in A column.
View 9 Replies
View Related
Jan 5, 2007
Is it possible to hide the contents of a column that would only show once clicked on?
That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
I have uploaded an example excel spreadsheet : example show hide.xls
View 9 Replies
View Related
Apr 2, 2009
I have a workbook that I want to show or hide a selection of cells depending on another cell when printing it.
Example
If cell x = A2A then display a certain cell set if anything else then exclude cell set from printing.
View 2 Replies
View Related
Jul 27, 2013
I've seen excel sheet view like this [URL].....
View 2 Replies
View Related
Mar 8, 2014
I'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:
[Code] ......
Why the above code does not respect the Master User "Jane" should be able to see all sheets?
Original source for this code was found here:
HTML Code:Â [URL]....
View 7 Replies
View Related
Feb 24, 2009
I'm creating an excel workbook that will allow any business or accounting student to go in and create their four year plan while giving them all of their requirements for their particular degree.
One of the features I wanted to have with this program is that it wouldn't display all the worksheets for each major, but rather the ones you choose that you are associated with. I was going to have a drop down menu on the first page that allows you to choose your majors, those being accounting, or business administration with its 5 focuses.
If I just allow all the worksheets to be shown, there would be 6 in total and I thought it might get confusing for a student who's only taking one of the 6. My hope is that on the first page, when you choose one of the options, it would automatically display the hidden worksheet that goes along with that major.
View 6 Replies
View Related
Jul 1, 2013
Basically, I am looking for a macro code to link to an 'Update' button that will do the following;
I will have 2 sheets, one sheet will be referencing data from other sheet with the following
=IF(Log Sheet!A4="Yes",(A1),"0")
This means, if it needs to be 'referred' it will go to the other sheet, and if it doesn't, then it wont and it will show up as 0.
Basically, every row will have this type of formula for 30 columns, and i would like a macro code that will hide values of 0, but even once hidden, will then show them again if in the other spreadsheet i change the value to yes, meaning i want the previously hidden row to show.
View 1 Replies
View Related
Jul 2, 2009
I'm trying to create a macro that will hide all the rows where the value in column E is equal to zero.
I'm currently using rows 1:700, but I may add to it.
View 9 Replies
View Related
Nov 19, 2009
I have looked through alot of this board and see the codes to hide all tabs old and new and code for each sheet.
My question is:
Is there a macro that can be used to do this as well. Say a button to hide all tabs and a button to show all tabs or does it only hide/show when the workbook is re-opened?
View 9 Replies
View Related
Feb 17, 2010
I have a spreadsheet that has been set up with totals at row 1010. The data that the spreadsheet contains only goes up to row 159. However data will continue to be added row by row over time. At the moment I have to keep hiding and unhiding rows to check the totals. Is there a way so that the spreadsheet will automatically hide all but five rows between the last row with any data in and the totals at the bottom of the spreadsheet?
View 9 Replies
View Related
Sep 18, 2008
I've been trying to make use of 'Run "doit", but the macro stops when it gets to my sheets called 'RST' and 'RST Pivot'. What I'm I doing wrong?
Sub DoIt()
Application. ScreenUpdating = True
With Sheet1.Shapes("Rectangle1")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
End With
'Toggling sheets Forces Rectangle 1to show while code is running
Sheets("RST").Select
Sheets("RST Pivot").Select
End Sub
Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?
View 3 Replies
View Related
Jun 26, 2006
I have a worksheet with 4 sections of rows. Each section I call Goal 1 - Goal 4. I want to show each section and hide the other sections by clicking on a button. I have used a column, P, to denote which Goal a row belongs to by entering 1, 2, 3 or 4 accordingly. This is the code I am using.
Private Sub ToggleButton1_Click()
With ToggleButton1
.Caption = "Goal 1"
End With
Dim rCell As Range
If ToggleButton1.Value = True Then
For Each rCell In Range("P2:P99")
rCell.EntireRow.Hidden = rCell > 1
Next rCell
3) Else
Range("P2:P99").EntireRow.Hidden = False
End If
End Sub
I have three questions -
1) This code works to show Goal 1 and by changing the >1 value to <4 I can make it work for Goal 4 - but I can't work out how to show the other Goals, 2 and 3.
2) Is this code an efficient way of doing what I want?
3) How can I make the "up/down" state of the toggle button actually relate to whether or not I am showing a particular goal? That is, if I click Goal 1 and then click Goal 4, I am showing Goal 4 but both buttons stay in the "down" state. I want the Goal 1 button to automatically return to it's "up" state when I click on another button.
View 2 Replies
View Related
Aug 8, 2006
code to write down to make a checkbox hide the text in a few cells..
What I want is..
When I tick the check box I want it to show me the text in the cells (C11:D11 to be specific) and then when I untick it I want it to hide the text so all you can see is the background colour.
Here is the attatchment of what I have so far:
View 4 Replies
View Related
Dec 13, 2006
I need some code that will do the following in a UserForm:
If option1 is selected = textbox1 is visible, textbox2/combo1 is hidden
If option2 is selected = textbox2 is visible, textbox1/combo1 is hidden
If option3 is selected = combobox1 is visible, textbox1/textbox2 is hidden
View 5 Replies
View Related
Dec 28, 2006
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden.
This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Cancel = True
Application.EnableEvents = False
Sheets("Protected Content").Visible = True
For Each ws In Worksheets
If ws. Name <> "Protected Content" Then ws.Visible = False
Next ws
Me.Save
For Each ws In Worksheets
ws.Visible = True
Next ws
Sheets("Protected Content").Visible = False
Application.EnableEvents = True
End Sub
View 3 Replies
View Related
Jan 5, 2007
if it is possible to hide the contents of a column that would only show once clicked on?
That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
View 9 Replies
View Related
Jan 9, 2007
I have a lengthy column containing text information. Within this column are various gaps. ie: several cells with no information.
eg:
..... Row F
1...text info
2...text info
3
4
5...text info
6...text info
7
8...text info
I would like to create a simple macro (switched via toggle switch) whereby it hides/unhides the rows containing cells with no text information.
It should also be mentioned that this column contains various background color formatting, for both empty and text cells.
View 4 Replies
View Related
Jul 24, 2007
I have a standard laid out spreadsheet, but in column C, D and E, there are comments in each cell which are filled with a lot of text. I would like to set a macro so that whenever an 'a' is in column A, the comments for that row are shown/hidden (the 'a' being a tick symbol in Webdings). So if I place an 'a' in A4 and A23, and click a show/hide button, it causes the comments in C4, D4, E4; C23, D23 and E23 to be displayed, and when it is clicked again (or it could be a separate button), they disappear. If no row has an 'a' in the A column, then the button has no action. I have recorded a Macro of me showing and hiding comments, but of course that is for a specific row.
View 6 Replies
View Related
