i got the userform to popup when the workbook is open and the buttons work fine... only problem is when i click on a button, it will ask the user yes or no... if the user press yes, it performs the required macro without any problem... but when the user press no, not only it will close the question, but also closes the userform... I would like the 'no' to close the question but not the userform...
View 4 RepliesI have a chart that I just added some dynamic text boxes to. That is the text is linked to a cell so that the text is automatically updated.
I also have a scroll bar that is used to adjust one of the cells. However, now once I click on the scroll bar it just disappears. I can click on the space where it is located and it will appear for a split second. Also, if I scroll down in the worksheet and then back up over the scroll bar the image will be refreshed and I can see the scroll bar again.
I didn’t have this problem until I added the dynamic text boxes, it worked fine with static text boxes.
Does anyone have any idea how to get around this, or do I have to stick with the static text boxes?
I made a template file with a userform included to enter data into a protected sheet at the command of a button. When I open the template, everything works fine. However, when I use a macro to create a new workbook and import the template to create a new sheet (or several) the userform suddenly disappears! The template is saved as macro-enabled and the new workbook is saved the same way. What am I missing that I'm loosing the userform??
View 9 Replies View RelatedWhen I click on the userform that opens when the workbook does I get this error:
Run-time error '13':
Type Mismatch
This is the code highlighted
DT=UserForm1.TextBox3.Text
how to get a userform by double clicking a cell.
I have created a userform, (my first one)-very simple form, i have created a massive textbox, so when i doubleclick a cell in Sheet 1, userform pops up and I can write comments.
I am not sure how to write a code for this? I know it will have Private Sub Doubleclick, not sure on the rest?
I need to make the Red X at the top firght of my user form "End" rather than "Exit Sub" which I think it is trying to do now, as simply exitting the sub returns it to the previous sub which opens the form again.
View 4 Replies View RelatedI would like to make a userform where additional textboxes (and labels) can be added to the form by clicking a command button. Is this possible to do?
For example, a userform which has:
Contractor 1: (TextBox)
and underneath this have a command button which when clicked will add another text box e.g.
Contractor 2: (TextBox)
I'm new to vba so haven't got a clue how to go about making this userform or even if it is possible.
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.
View 3 Replies View RelatedI begin in the Word of VBA. It's the first time I've a formula which seems necessitate VBA to be written down.
Before to try to execute my complicate macro, I've begun with simple tricks. But I've got a strange comportment of Excel :
I've the following Sub :
I have created an Excel spreadsheet with data. I froze the top row and added filters. This document will be used by numerous people, so I would like instructions for them on how to update the data on the same page as the data.
I put the text box off to the right of the data, but when the data is filtered, the text box is hidden along with the hidden rows.
Is there a way to have instructions to the right of the data that will not be hidden when applying filters? It does not have to be a text box but it does need to be on the same tab.
I have an auto filter setup on some data. When I perform an advanced filter either in-place or to another location, the auto filter disappears. Is it supposed to be have like that? If so is there some sort of work around?
View 2 Replies View RelatedWhenever word wrap is applied the text is not visable in the cell. It
remains visable only in the bar above.
I have already confirmed autofit and it reduces the column to 1 character
wide.
I tried detect and repair on the installation and nothing.
I even upgraded from Excel 2k to Excel 2003 and no change.
My Workbook contains the following macro in Sheet 1, which displays a pop-up calendar in L15 when that cell is selected:
Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.Select
Calendar1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("L15"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub.........
I have a large spreadsheet with many cells that have a drop down list using data validation. The list is a named range with no blanks. Most of the cells show the default I have set. However, sometimes when changes occur in cells next to one with a drop down list, the default value mysteriously disappears and the cell appears blank. The functioning of the drop down lists is intact, it's just that the cell appears blank.
View 5 Replies View RelatedI add data validation into 4 columns and when i re-open the spread sheet after saving it, it clears all validation.
Although it clears the validation, the place where it gets the data from is still there. i have also unfroze panes and is still clearing data validation
Attached is the spread sheet. The data validation is in columns A, G, H and I on the 'unsourced' page and is getting the data frfom the 'Sheet1' Page.
using Office Professional Plus (2013). Within my spreadsheet, I have a series of progressive dates to be used in as a timeline. Ultimately, what I need to accomplish is that when I change one date, all the proceeding entries update according to my timeline criteria (in this case 3 weeks or 21 days). I created a SUM formula that worked but only calculated one time. Once the calculation completes, the cell drops the formula. I need this to be constant as the dates often change. How do I get the formula to maintain? Further more, is there a better method to accomplish the task at hand other than the SUM option?
View 7 Replies View RelatedI have the same problem as Paul C in the following post:
(Pictures disappearing)
"Hi not a VBA problem, but when i insert pictures or logos into my workbook they disappear, i can insert a picture close the workbook but when i re-open it the picture has gone, i have to click on where it should be to get the border to appear, the move it to get it to appear.
I have tried inserting BPMS, JPGS, GIFS, but i have the same problem with them all, I have used the bring forward command but still have the same problem, I have even tried re-installing Office 2003 and installing all the latest updates but still get the same problem, any ideas?"
I have tried:
- Tools > Options > View > Objects > Show All
- Changing macro security level
- disabling macro security in my antivirus
On a sheet, I have a Worksheet_change sub that checks if the cell changed is in column 1, 10, or 19 and if it is, then the six cells to its right are filled with formulas I need. All formulas work, except for one. I've checked over and over again and the formula itself works fine - the coding behind it is exactly what it's supposed to be.
But for some reason sometimes the formula isn't put into the cell. I haven't found any pattern yet with this problem. Even stranger is that the formula is identical to one in another column before (which is also entered by the macro) and the other one has never disappeared yet. I'm setting both formulas with the abc.FormulaR1C1 property (I don't use the abc.Formula property to avoid having to write three different formulas - one for each possible column change).
I'm in the process of writing/editing a macro, but after I run the macro once, the Expressions in the Locals Watch window disappear, and if I try to run it again it produces an "Excel has encountered an error and needs to close". The macro probably doesn't make much sense out-of-context, but perhaps there's a glaring error that's causing this. A guess - possibly related to the Error Handling in the vba? (1st time I've used this in a macro).
Option Explicit
Sub Compare()
Dim strNaspK As String
Dim strCtryK As String
Dim intCtryRev As Integer
Do Until ActiveCell. Offset(1, 0) = ""
ActiveCell.Offset(1, 0).Select
strNaspK = ActiveCell
strCtryK = ActiveCell.Offset(0, 2)
On Error Goto ErrJump:........................
Basically, I've got two sets of data on different sheets, with NASP ID and country being variables. Just trying to import the data from the 'Comparison' sheet, if both the NASP id and country matches that on the first sheet, established by the variables.
Excel 2007. Basically, I'll be working in excel and all of a sudden, my screen sort of blurs; gets scramble like below. This only happens when using Excel.
Myscreen.PNG
I have a workbook I update from numerous sources every day. A few months ago I noticed some of my formatting started to disappear - a couple of borders were missing. a day or two later a couple of cells which had color were no longer colored. number formatting reverted to general so my percentages just showed up as decimals and my dollar values no longer had a $ or commas. Eventually - over the course of several weeks - all formatting in the document was completely gone. A couple of times throughout this process I tried replacing all the formatting, but as soon as I'd save the file, close it and open it back up whatever formatting related changes I had made would again disappear.
Eventually I went back a couple months and pulled an old file which still had its formatting and updated that instead. This worked fine... for about 2 months. three days ago I started noticing the same issues - a few borders disappeared in the file. two days ago a few more borders were gone and a couple of cells lost their color. yesterday and today it has gradually gotten worse again.
I have written a macro. The macro in question simply shows a user form. There is far more code within the user form itself. It is designed to automatically format a workbook.
I saved the macro as an .xlam add-in file to the default folder. I then added a button for the macro to my Quick Access Toolbar. This seemed to work great.
However, when I open a new file that I want to run the macro on, the button disappears. In fact, a 2nd Excel window opens up on my task bar. The original window still has the macro button, but the new window does not.
In versions prior to Excel 2007, I was always able to copy a chart when I copied a tab. However, when I copy a tab in 2007, the data copies fine, but the chart does not. Something similar happens when I try to move a tab with a chart on it to another workbook.
View 5 Replies View RelatedI have a very large spreadsheet which holds a lot of data, and has a custom reports system built into it, (i.e. running on a load of macro's)... The reports gather their info from a range of hidden cells which run different formula's to provide such results as 1 or 0 so that it collates into another sheet...
however, I have found recently that when I need to update any of the formula's and whatnot, the formula does the calculation and then disappears... whereas I need the formula to be there constantly so that the report is providing the correct information!
one such formula is:
=IF(AND(AC11"No Calc",AC11>=1,AC11
I have a lot of text in a cell and I want to create a Hyperlink using only 2 or 3 words and not the whole text. Unfortunately, it seems as though the hyperlink can only be from the cell itself. Is it possible with Excel to create a Hyperlink from a section of text in a cell, and NOT from the cell itself?
View 4 Replies View RelatedI'm trying to make a code so that when optionbutton1 is checked, it allows you to right click to select a cell, then right click on a different cell and select that cell as well as the previous cell, etc. Here's What I have so far.
[Code] .....
I would like to run a macro by clicking a cell. I don't want a toolbar button/menu item and Hyperlinks don't work. Can this be done?
More info: My macro selects the current row in Sheet A and moves it (cut & Paste) to Sheet B in the workbook. The macro then goes back to Sheet A and deletes the now blank row.
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z$29:Z$39)-1)
Above is the formula that I have, I am putting it into Column L. When I do auto fill every row that has data in J and K the formula goes into as you would expect.
However the formula does not activate until I double click inside each individual row and hit enter. Therefore if the calculation in L4 was 1.0, then 1.0 will appear as the calculation in every row of Column L until I double click in each row individually and press enter.
Is there a way to disallow certain cells to be clicked on?
View 9 Replies View RelatedI have this data sheet with 7 personnels name in it. What I require is that upon clicking on the name of the person, the profiles that are attached to this person will open up.
Before any name is clicked, these sheets should not be visible to the user and upon clicking on the other names, the sheets that was previously open will disappear.