1. I'm looking for a better way to get a UserForm (frmParts) to show right from the start as soon as I open the workbook while hiding the application. I use the following when I initialize the application:
Code:
Application.Visible = False
frmParts.Show
2. Later on, I add some parts to my inventory, but if I don't make the application visible, it doesn't save the data. So, I have to make it visible and then hide it again. Is there a right way to accomplish the save without having to unhide/hide the application? This is the applicable code I used (ComboBoxes & TextBoxes involved):
Code:
'copy the data to the database
Application.Visible = True
Application.ScreenUpdating = False
I have two workbooks (Book1 and Book2). When Book1 opens, its Workbook Open procedure displays a Userform1 that has two Command Buttons. The first Button closes Book1 (exit the program). The second Button opens Book2. Book2 has a Workbook Open procedure that should close Book1 and display a new Userform2. When the Workbook Open procedure of Book2 closes Book1, the procedure stops there and does not show the UserForm2. How can I get the Workbook Open procedure of Book2 to execute both steps of closing Book1 and showing Userform2? This must be done by clicking the second Button on Userform1.
There's a sheet called "Main" and 38 other sheets that shows the data (these 38 sheets all have the exact same structure/layout). Let's say these 38 sheets are called "country1", "country2", .... "country38".
Based on user input in cell J10 in "Main", I want to show only the relevant columns in all of the 38 country sheets. Specifically,
If J10 in sheet "Main" = "Option 1", hide all columns in all 38 sheets except columns A to W.
If J10 in sheet "Main" = "Option 2", hide all columns in all 38 sheets except columns Y to AU.
If J10 in sheet "Main" = "Option 3", hide all columns in all 38 sheets except columns AW to BS.
If J10 in sheet "Main" = "Option 4", hide all columns in all 38 sheets except columns BV to CQ.
If J10 sheet "Main = "ShowAll", show all columns in all 38 sheets....
Cell J10 in "Main" will be a drop down bar with the 5 choices.....
In my sheet, I've got a command button that protects the sheet and unprotects it (code was thanks to this forum) and I'd like to make it so that when the sheet is protected, all the other command buttons are hidden (CommandButton1 to CommandButton5) and then when the sheet is unprotected again, the buttons all show up.
The reason for this is because when other buttons are clicked as the sheet is protected, it brings up the error and shows Debug as an option and then the user can click that to look at the code and password for the protect sheet button.
I have a some simple code that doesn't seem to want to work ALL the time. Granted, sometimes it works but not always. The first code is to hide a bunch of columns and the second is to display those again. I put in the If/Then to avoid trying to hide columns already hidden (I thgouht that had to be done - true). Anyone see any problems with this code. The error I get is:
"Run-time error '1004': Unable to set the hidden propoerty of Range class. I get it at
If wb.Worksheets("Growth").Columns("ap:iv").EntireColumn.Hidden = True Then wb.Worksheets("Growth").Columns("ap:iv").EntireColumn.Hidden = False 'Error is on this line End If..............
Is there a way to create a shortcut/button by hiding or showing a column? Example: I want to hide or show the whole column E by pressing a assign button or shortcut.
I am trying to hide/show a static set of rows based on the value chosen within a combobox.
The combobox has a list of names and one blank. The blank is selected by default, which renders the rows hidden. When a user selects a name in the combobox the rows will be shown.
I got some help on this, which allowed me to hide the rows when the workbook was activated and when the worksheet was activated, but the code to show/hide the rows based on the combobox value is not working.
My macro is called from a command button on a userform. The userform contains 3 command buttons, a label, and an image box with no attached image. When a command button is pressed, the code sets the background colour of the image box to red and changes the label caption to "Processing..". Then screenupdating is set to false, the code runs, screenupdating is set to true, the image turns green and the label says "Ready!".
The problem is that the userform does not refresh to show the changes until the code is finished running, as though the screen does not update the userform before the "screenupdating" is turned off. Can anyone tell me how to ensure that the forms are displayed correctly before the screenupdating is disabled?
I have a macro with the following code at the start:
Unload UserForm2
returnvalue = MsgBox("Are you ready to update your Records? Did you enter all the bowler scores? Did you change the bowler's Status?" & Chr(13) & "If you answered Yes to all questions, then click YES, otherwise, Click NO & make your corrections.", 36) If returnvalue = 6 Then UserForm7.Show vbModeless Application.Run "Records1" Else Exit Sub End If
Userform7 loads but the TextBox with the text 'Please Wait' doesn't show. how to show the TextBox.
I have a small userform with a ComboBox (ComboBox1) which, on a change event, kicks off another process. It's working fine, but the problem is that the userform hides immediately, and the open combobox remains visible while the second process starts running. It's only like that for about 5-7 seconds, but it doesn't look overly professional.
I'm using MS Excel 2007 on WinXP.
I tried putting in a sleep command or two, but that doesn't quite do the trick (the sleep is for 1 second).
I have 2 forms (A and B). Form B is initialized with values from a global array created in form A. When closing the B form it's still visible. I want it to dissapear.
The B form is opened from the A form
Private Sub cmd_ok_Click() frm_B.Show End Sub
When closing the B form it's still visible (modal)
'Within form B Private Sub cmd_cancel_Click() Unload Me End Sub
I have a workbook which when opened connects to an external datasource (excel file) and downloads a table of information. While this is happening I have a userform which shows which has a lable saying (connecting to datasource, please wait....). However, when I open the workbook, the userform shows but the labels do not show, the userform just appears white.
I'm trying to use a non-modal userform as an application option board. Using API, the initial (Activate event) form transparency is set to 10%, and the Userform_MouseMove event turns it to 100%. The problem is that I need the transparency back to 10% (hiding it would be enough) whilst mouse remains outside the userform, and I've no idea of how I can get it.
I am not sure if this can be done or not. I am displaying a userform non-modally (Modeless). It will remain in place while the workbook is open. I know how to stop the form from being removed if the user clicks on the close box (the X in the top-righthand corner). What I would like is to stop the X displaying at all. I heard that it might be possible in the Properties box, but cannot see it in Excel 2000.
Even better would be if I could hide the entire header of the user form, so that I could simulate the look of placing controls within an Autoshape. (I am producing a project for users of Excel 2000 onwards, and I understand that placing controls directly in a worksheet can cause problems in other versions, so I must use forms.)
I created UserForm with one TextBox and "Close" Button. And it's works like a real-time search with my criteria. He is using AutoFilter to show what he found. And when I'm pressing Close Button the Focus isn't on Application, but on Hidden window (know because when I'm clicking alt+space form's menu shows).
To show window I'm using (running from hot-key):
DoCmd.OpenForm "UserForm1"
to hide
Private Sub CloseButton_Click() UserForm1.Hide End Sub
On opening of my WorkBook I refresh my PivotTable and that takes a while so while its refreshing. So I want to launch a UserForm that basically have a loop showing progress bar similar than the Windows loading bar (i.e. a set of green vertical green bars moving in a black rectangle).
is it possible to enter data on a spredsheet while a form is opened or must the from be closed first. I thought I was smart to have a form remain open so users could change parameters but at the same time allow them to enter data in uprotected cells.
The attached spreadsheet is as simple as it gets - a single userform with a combo box that allows 1 of 4 choices. It gets loaded and displayed in the workbook_open() event - or at least it should - but 9/10 times i get the message "Path/File error" and debug crashes out on the userform.show line. The next time I try and open the file (despite not saving it) Excel crashes and closes! Even re-starting Excel does not allow the file to be opened!
If, however, I open the file with macros disabled, and go into the VBE and manually run the workbook_open() all works fine. I'm using Excel 2003 and I've tried it on other installations - sometimes it will work, other times not.
I've tried every combination of userform load, unload, hide & show with similar results.
I have a wordlist (65000 words in Column B*) in a worksheet “w1” and poems (about 21000 rows) in another worksheet “w2” where first verse is always in B and second one in C. The column D of w2 contains of information such poems name, author’s name, book’s name and so on.
I want to search for those verses through a macro which contain words from my wordlist B* w1 and add references to my words that way. It’s about making a dictionary in which each word has a reference to a poem and verses in which it is used.
Column A of w2 has number in it which shows how many times these rows have been already used as reference. Column A of w1 contains of a value “1” or “0” in which “1” means this entry has already been processed or already has a reference and “0” means it has yet to be done.
If my word B* from “w1” exists in verses B or C of “w2” the results should be shown in an userform containing 5 text boxes with 5 results. In each text box a result should be shown “(verse B; verse C); (value of Col.A)” and a checkbox (or a button). That would mean 5 result, 5 textboxes and 5 checkboxes (or 5 buttons). I would like then to choose one of the results by checking the checkbox (or pressing the button) in front of the text box. After I have chosen the result the whole row from w2 should be pasted in following columns of B*. That means the cells from w2 column B,C,D would be pasted to column C,D,E of w1.
The criteria for the search in B and C should be the lowest value in A of “w2”. I mean the w2 rows with lowest A value should be preferred if there are more results. The result showing user form with 5 text boxes and 5 checkboxes(or 5 buttons) which gives me the choice to choose one of the results should also contain a button “search for further results“. For the case none of the results is useful.
As far there are many poems (verses) I would like to prevent using the same verses many times. The more different verses I use as reference for the words in w1 the better it is. Therefore every time a result is picked by me “1” should be added to the value in Column A in w2. And every time search is started the lowest A values should be searched first.
As soon a word has successfully got it’s reference the value in A w1 should be changed from “0” to “1”. And by next search all words with A value “1” should be ignored and only “0” words should be searched for.
Each time a row from w2 is used as reference, the B* word w1 should be added to col. E of w2. If used for many ";" should be the seperator.
I have a spreadsheet set up to automate calculations and control data input for in process quality control checks. The spreadsheet is basically a series of input boxes requesting information from the user. To control the input of part numbers I have some code for a userform which populates listbox within the userform from another closed spreadsheet (opens & closes without updating screen) & enters the selected data from the listbox into a selected cell in the spreadsheet
all the input boxes and calculations are working well, the userform works well, BUT when I include the userform within the code I get the following problem, an error saying that I am trying to change data in a protected cell where data has been entered and then protected by the VB code, even though the code is not trying to change the cell.
I have tried just loading and unloading the userform and everything works well, it only fails when I actually use the userform, and then close it. from this I am fairly sure the problem lies within the form so I have been through the form code line by line and deleted anything that is not vital and the problem persists. Here is the form
Private Sub CommandButton1_Click() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Dim Part As String
Part = ActiveSheet.Range("B1") Msg = "Are you sure you want Part No" & vbNewLine & Part ' Define message. Style = vbYesNo + vbDefaultButton2 ' Define buttons. Title = "SELECT PART No." ' Define title.
Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User chose Yes. Set_Data_1 ' Perform some action.................
I and a colleague are working on some code to automate a web page. We've been able to get it to fill in the needed forms, run a report which comes out in Excel format and save the output to a specified shared drive location.
However, when we attempt to close the resulting window we are unable to...
The webpage creates a new Internet explorer window which we're able to capture and do a wb.saveas, but when we do wb.close, it does not generate an error, but the new window does not close.
In the VBE, the workbook is visible as an object, but in Excel if you go to Windows on the main menu and attempt to select it, it's not listed and the Unhide option is greyed out.
So the object is a member of the workbooks collection for the VBE, but not Excel...
I regularly utilize .xlw files to open sets of workbooks all at once. Usually, they're made up of 10-15 workbooks, using the cascade arrangement.
Problem is, each time I re-open my .xlw file, the workbook that was supposed to be in the top is hidden. If I re-configure and re-save the .xlw file, the top workbook still disappears.
If I open the native file for the top workbook, it is not hidden when I open it.
I have a workbook which will open another workbook on a server, but i see it on the open and close on the task bar. Because I have 4 pc's with the main workbook open as single applications and one book on the server which collects info everytime a button is pressed, I would like it not to show on the on the task bar, needs to be invisable, is there any way of this?
Also
Can i see if a workbook is open, regardless of which PC (out of 4), which may not be the one i am using. So a way to see if a workbook is open anywhere else?
I have downloaded a custom menu workbook attached. I am wanting to only show the custom menu in one workbook as it appears in all workbooks open at the time.
I am trying to write a macro that will do the following:
Copy workbook "X" as a new workbook, but name the new workbook from a cell entered in cell B2 of the newly copied workbook. The final process is then to hide workbook "X".
I have an Excel 2007 workbook that's been used for 5 months or so now. Recently, people I email it to say they can only see one worksheet in the workbook when they open it, and are unable to switch to any other worksheets in the workbook (they can't even see the tabs). They are in the "normal" view, and I have no idea what else could be wrong. Did I mess something up with the protection? Any other ideas to check?
I used to be able to see on the bottom bar that there are several Excel files that are opened. There would be a small black triangle next to the Excel button below. But now I need to do click the 'Window' button above to pull down the menu to access to other Excel files that are opened. This problem is only with Excel but not Word or Powerpoint? Is there a setting I must do in Excel? Auto Merged Post Until 24 Hrs Passes;
I have the following code that displays a form at a user defined time and if the user does not press "Stop" then the workbook saves and closes. The user can press stop then the workbook remains open.
Here is what I have where: Admin_Auto_Shutdown = Yes or No Admin_Auto_Shutdown_Time = 3:34pm or user defined time (This doesn't seem to work??)
'Auto Shutdown CloseandSave If UCase(wb.Worksheets("Admin"). Range("Admin_Auto_Shutdown").Value) = "YES" Then Application .OnTime TimeValue("Admin_Auto_Shutdown_Time"), "AutoShutdown" End If
Now, my question is about a timer that I can show on a form. When the form is displayed I would like to give the user 30 seconds to press stop (and keep the workbook open) or to press proceed and save and close or to not do anything and the workbook would close and save when the timer reaches zero.
Code for user form which is missing most everything...
Private Sub Halt_Click() 'If user whats to continue without closing Auto_Shutdown_Form.Hide End Sub
Private Sub Proceed_Click() 'If user whats to save and close
Auto_Shutdown_Form.Hide
How do I add a timer to this code where it will run this at the end of the timer?
Auto_Shutdown_Form.Hide
Application.DisplayAlerts = False With ThisWorkbook .Saved = True .Close End With
