how to show tooltip for UDF in runtime. In excel if u type for function Vlookup by pressing key =vlookup( ) the moment we press opening bracket "(" excel itself shows the arguments to be entered.
View 9 Replies
I am having a small problem with microsoft excel 2007.the problem is that: my excel 2007 cannot display tooltip of the column width when I keep and drag left mouse
Show tooltip of the column width as attached file.
I also tried many ways to find settings of advance menu. But cannot.
Pic.jpg
Ive created a userform that I want to have input data into specific cells in my worksheet. I have the userform created, and some of the other VB stuff written, but it is far from being functional. I have come across the first of what to is to be many problems (Im pretty rusty at VB).
I have a button on my spreadsheet that is supposed to show the userform using this macro
[Code] .....
When I click the button, it breaks at "Form1.Show" giving me "Run-time error 424 : Object Required".
I have attached a screen shot of vlookup formula. Whilst selecting a range a little tooltip comes up showing how many rows and columns have been selected (6R x 2C) ...
... at least that's what used to happen. Now this handy tool tip does not show on my PC - and I have no idea how I turned it off, or more importantly how I can turn it back on again.
I am running Excel 2002 SP3 on Windows XP.
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
I have a column of data held in column B.
I am required to show the following.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
do I need a macro?, or conditional formatting?
The bug I'm speaking of has come up a few times for me in diffrent situations.
Its seems as Excel isnt updating what it does properly according to the macro runtime. An example:
Application.DisplayAlerts = False
Application. ScreenUpdating = False
Application.EnableEvents = False
CurrentSheet = ActiveSheet. Name
CurrentBook = ActiveWorkbook.Name
This is just the first few rows of my macro. I want to be able to run my macro, and then leave the user at the workbook and sheet he/she was working on before.
If I let it run, the CurrentSheet will get a sheetname that is hidden, and NOT active. I havent even started to touch it yet with my macro, but a webquery is run on it, and the macro is run on the Change- event on that sheet.
If I set up a breakpoint on the line before I give the variable CurrentSheet its value, I get the sheetname that is currently active. I.e. During runtime Excel does not update itself before it continues. In debug mode it does. This is most annoying, since my code should work after what I know, and it DOES work in debug mode, but not in runtime.
if I leave the inputbox blank I get an Excel error. I.e. it says that the formula I tried to type contains an error..... Is the Type:=8 not working properly here?
Dim Addr As Range
On Error GoTo Problem
Set Addr = Application.InputBox( _
Prompt:="Enter or Select a cell in the last record", _
Title:="Select Last Record:", Default:=Selection.Address, Type:=8)
If Addr Is Nothing Then Exit Sub
Edit: I guess I should mention that I am trying to retrieve a cell reference from the user via an input box. This cell reference will help me determine the last record in the spreadsheet.
In Excel 2003 I have made a model for evaluating co-workers. The model consist of a number of different questions regarding performance. At the top of the sheet, the user have to select som data from a drop down list, and depending on the answer (upward or downward), som of the questions in the sheets is hidden or shown. For this purpose I have created this string of VB-code in the sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$5" And Target.Value = "Upward" Then
Rows("20:41").EntireRow.Hidden = True
ElseIf Target.Address = "$E$5" And Target.Value = "Downward" Then
Rows("20:41").EntireRow.Hidden = False
ElseIf Target.Address = "$E$5" And Target.Value = "" Then
Rows("20:41").EntireRow.Hidden = False
End If...........
He's the issue i'm getting with the code...
Public Sub Auto_Open()
wrkbkName = Application.ActiveWorkbook.Name
'this little snippet of code determine which branch the user is at
'based on the ip address
'if ip is like 192.168.1.xxx then we on welthsrvr
ipadrr = GetIPAddresses(True)
If Left(ipadrr, 9) = "192.168.1" Then
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("G9:BF94")) Is Nothing) Then
With Target
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
End If
End Sub
However when ever I select more than one cell which already has text in it and press delete I get a runtime error 13 and the debugger highlights this line:
.Value = UCase(.Value)
I get a runtime error 9 on my excel form.
This line is highlighted:
Workbooks("Proposal for XL.xlsm").ActiveSheet.Range("S13:AH13").Copy
I have created a workbook for external users that allows them to track data on a monthly basis and submit to me. Each spreadsheet has a code to sort names alphabetically. Here is the
Sub Button53_Click()
Dim i
For Each i In [B10:K309]
If i.Value = 0 Then
i.Value = ""
End If
Next
ActiveSheet. Unprotect Password:="xxx"
Range("B10:AB309").Select
Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollRow = 10
ActiveWindow.SmallScroll ToRight:=-5
Range("B10").Select
ActiveSheet.Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Everything works great for the majority of users, but those who are using Excel 97 return a runtime error on the following line: If i.Value = 0 Then. This code was created in Excel 2000 since that was the software our office used.
I'm getting Runtime Error 91 on this line:
destRange = Range(.Cells(destRow, 2), .Cells(destRow, 7))
Here is the overall function:
Public Sub sendRow(myRow As Integer, destinationSector As Integer)
'************************************************************
'1. Copy Row... Cols 2-7
'2. Find First Empty in new Sector
'3. Paste Row on first new Row
Dim myBounds() As Integer
Dim destRow As Integer
Dim destRange As Range
Dim cutRange As Range
Dim y As Worksheet
myBounds = getSecBounds(destinationSector)
This subroutine is being called from the Worksheet_Change event, and should therefore have an active sheet.
Is there a limit on the amout of pictures you can use with the statement me.pictures.visible? Reason being, I am trying to import 119 pictures into a spreadsheet. When the information is selected form a drop down list, it pulls up the 1 of the 119 pictures. I was able to get 54 pictures input. Everything was going great until the 55th. When it stared giving me an error...
______________________________________________
Runtime Error '1004':
Unable to set the Visible property of the Pictures class
______________________________________________
When I select Debug, it takes me to the line with Me.Pictures.Visible = False
Everything seems to work fine until I enter the 55th picture. If it is that, is there a viable work around?
Getting a error message when opening a workbook "Runtime error 1004" I dont know much about excel and the user explained it to me as best she could . Here is where the script bugs:
Sub auto_open()
fdist = ActiveWorkbook.Name
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Worksheets("f_cumul").Cells.ClearContents
Worksheets("f_cumul").Cells.ClearFormats
Worksheets("vtes_dj").Select
Range("a1").Select
Selection.End(xlDown).Select..............
I have read the other posts on here and swapped, Dim timeinc As Interger to Dim timeinc As Long and Dim timeinc As Double but neither worked. They both make the calculation 0 and I can not figure out why. This macro has worked before and is failing only on this new dataset which I have attached as well.
I am solving this problem on a PC and the macro was built for a Mac but I do not think that should change much as when I run it on a Mac it fails the same
I've been having some problems with my company's spreadsheet in terms of calculations using Add Ins.
Some of the functions work within the spreadsheets - others give me a runtime '9' error,
I have an application using two userforms. Following sequence is used:
1. Open Userform1 (with uf.show)
2. click on a commandbutton in userform1 opens a second userform on top of userform1.
3. Close userform2 with commandbutton on userform2 (userform2.hide)
(I did not want the user to close the UF via the "X" )
Runtime error 402 : Must close or hide topmost modal form first
So, I changed my code so when the user clicks the commandbutton in userform2
userform1.hide (which is the topmost)
userform2.hide
But again I get the same error.
I have a script that usually runs, but all of a sudden gives me a run-time error 6: Overflow error. Basically I'm searching for certain criteria and copying or cutting and pasting lines from one sheet to another. Why I'm getting the error by the code below?
Sub teal()
Dim i As Long
Dim qq As Long
Dim assignmentnum As Long[code]......
I have a userform that accepts some information and checks to see if the values have already been used (address and work order number) If the value is used the user must change the value ,an input box is triggered to get the new value. My question is how do I transfer the value back to the userform??
View 9 Replies View RelatedWhen I run the following code it keeps giving me a runtime error 1004.
Code:
Sub ImportData()
Dim fNameAndPath As Variant
Dim wb As Workbook
Dim cNextRow As Long
Dim Ans As String
NextRow = ThisWorkbook.Worksheets("Errors").Range("B" & Rows.Count).End(xlUp).Row + 1
cNextRow = ThisWorkbook.Worksheets("Compare").Range("A" & Rows.Count).End(xlUp).Row + 1
[code]...
I have a bug on a macro that was working until yesterday. I did not write this macro and I cannot seem to fix it. The debug stops at the below command. The data it looks at seems to be in the right format so I think there is something else that is wrong.
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
I am trying to create textboxes during runtime
The seceond For(c) is working, but not the first For (r)
This is my data looks like at the moment, it Changes.!
Name
Peter
Jan
Rose
Totalt
[Code]....
Code:
Dim r As Long, c As Long
i = 2
j = 1
For r = 1 To 4
[Code]....
I have this macro and I keep getting a 1004 runtime error 'PasteSpecial method of range class failed' on the highlighted line,
Sub Print_()
'
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Sheets("Print").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = xlAutomatic
End With
I have a workbook with some Change Event code posted below. It executes fine 90% of the time; however, cells with dropdown lists create an issue. If I double-click one of the dropdown cells (to directly type a value), then while my cursor is in the cell, click the small arrow to display the list, a 50290 (Method Intersect of Object Global failed) error occurs and highlights the red line in my code. Any ideas what this is about and how I can avoid it? (I've also included my Selection_Change event, as that's the only other code on this sheet).
Dim OldValue As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AD:AF")) Is Nothing Then Call Summary.summarize
If Not Intersect(Target, Sheets("ProjectData").UsedRange) Is Nothing Then
edited = Target.Row
SingleVal Sheets("ProjectData").Range(edited & ":" & edited)
End If
If Not Intersect(Target, Range("K:K")) Is Nothing Then
If Target.Value "N/A" Then
While InStr(1, confirm, "y", vbTextCompare) 1 And InStr(1, confirm, "n", vbTextCompare) 1....................
I am trying to display a pop-up when a macro starts and display the runtime in Seconds and should close when the Macro completes.
View 9 Replies View RelatedI am trying to create a userform during the progression of my code. I found this is the MS help website but it doesn't work for me . .
Sub BuildMyForm()
Set MyNewForm = _
VBE.ActiveVBProject.VBComponents.Add(ComponentType:=vbext_ct_MSForm)
End Sub
I am currently trying to develop a code for calculating the value of an asian option using a binomial tree... In running the macro I get a run-time error 6 - overflow message. I have put the code below. note that the code is still under construction - in order to check it I have put in some hard-coded figures. Of course, these should be calculated in the model eventually. The bug is in calculating the "InterO1" value ...
Sub bereken_asian_call()
sig = Sheets("Sheet1"). Range("B1").Value
T = Sheets("Sheet1").Range("B2").Value
N = Sheets("Sheet1").Range("B3").Value
r = Sheets("Sheet1").Range("B7").Value
div = Sheets("Sheet1").Range("B8").Value
S = Sheets("Sheet1").Range("B12").Value
K = Sheets("sheet1").Range("b13").Value
alpha = Sheets("Sheet1").Range("B14").Value
Dim St() As Double
Dim F() As Double
Dim O() As Double
Dim NewAv1() As Double
Dim NewAv2() As Double
Dim Ffut1() As Double
Dim Ffut2() As Double
Dim Ffut3() As Double
Dim Ffut4() As Double
Dim den1() As Double
Dim den2() As Double
Dim InterO1() As Double
Dim InterO2() As Double
dt = T / N
u = Exp(sig * Sqr(dt))
d = 1 / u
pu = (Exp(dt * r) - d) / (u - d)................
