I am trying to make inserting comments simple computer illiterate users :-) I have a spreadsheet whith a check box control. In the control I have entered this code
Private Sub CheckBox1_Click()
Dim Var As String
Range("L11").Select
ActiveCell.FormulaR1C1 = "See Comment"
Var = Application.InputBox(Prompt:= _
"Please Enter your Comment:", _
Title:="ENTER COMMENT", Type:=2)
Range("L11").Comment.Text Text:="" & Var
End Sub
What I am trying to do is have them check the box, which pops up an input box where they can enter their comments... and then copy this information into a comment field. I obviously don't have the synthax correct because I get an error whe I try to assign the variable. While I am asking... is there any way to have this program size the comment box large enough to show the entire comment?
On the user input page, there will be two fields to enter information in... one for an exact address (ex: 600 n. nowhere) and one for a casual address (ex: On Nowhere street south of Anywhere Blvd). On the records page, I'm going to keep all the addresses and their corresponding casual addresses so that in the future, I don't have to keep inputting both.
What I'm looking to do is have a formula/function/vba code check the exact address against my records for it already existing -- and if so, pulls the corresponding casual address. For example, if last week I entered 600 n. nowhere and then transferred it into my records and then this week, I again entered 600 n. nowhere, the workbook would find the record and place the casual address for me.
i am writing a program that asks the user for an input and sets it equal to a variable.
i want to look thru a column for this variable, if it is not in it, then paste it at the end of the column. if it is in already, throw up a dialog box saying its already used and exit the sub.
i dont know how to set up this kind of thing up???
•Write codes to pop up an InputBox to ask the user for a customer name.
•The program uses the user’s input to check whether the name is on the list. If it is, display an msgbox saying that the customer name is on the list, and the corresponding cell will be indicated in boldface and in blue. Otherwise, an msgbox will be displayed saying that the customer name is not on the list.
Dim the customer list as an array (string var. type) and Dim Found as Boolean. You will need the If-Then construction and For-Next or Do-While/Until loop too). Create a button to run and another to restore the formatting to its original style.
This is what i did so far and still having problems with it:
Option Explicit
Sub customers()
Dim Arr() Dim R As Integer Dim C As Integer Dim ReturnColumn As Boolean
what I want. It relies on an input box asking the user to enter a part code, which when found creates a duplicate copy. However, if the user enters a part that doesn't exist I get a run-time error. Can anybody point me in the right direction so when a user enters an incorrect part they get a message telling them it doesn't exist.
Private Sub SortTest_Click() Dim s As String Dim r As Excel. Range
Range("A2").Activate s = InputBox("Enter the number you wish to find") If StrPtr(s) = 0 Then MsgBox "You must enter an existing part number!" Else Set r = Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False)
how to copy the contents of multiple comment boxes and paste in a single comment box.
The big picture is that I have a number of cells with numerical values in and text in comment boxes. I want to be able to click a button to copy the contents of the comment boxes and paste them, along with the numerical value from the cell, into a single comment box, ordered by highest to lowest value within the comment box, then delete the original cells and comments.
I am quite new to VBA but have been coping quite well so far with information of the web and analysing recorded macros.
I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1) Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
I am just starting to get my feet a little damp with VBA and I am trying to make a macro that will act whenever any text is entered in any cell within a 3x3 square. Whenever any text is entered in any of those cells I would like it to enter a text ( "X" ) in a cell which is specified by a cell in the spreadsheet (this cell will output which cell the macro should write the text in in this format "A:1" or "C:3"). How can I make the program act only in reaction to the user entering thier text, and also, how can I get the macro to read A:1 and enter the text into that specific cell? Any help would be so awesome, the book I have is really difficult to navigate.
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts
8 digit should be "#### ####" 12 digit should be "###### ######" 13 digit should be "# ###### ######" 14 digit should be "# ## ##### ######"
these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code: Sub Tester()
Dim WB As Workbook Dim WS As Worksheets Dim modCounter As Long Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm") Set WS = WB.Sheets(BM18)
I am in need of VBA code so that I can fill out an input form generated by someone elses macro input box. (I am the user, not the one developing the input box).
I am working to streamline a whole cluster of macros and this input box is currently my bottleneck. When the input box pops up, instead of me manually typing, I want to autofill the space with "104" and then click 'ok' uisng VBA.
I can not modify the actual VBA that created the input box (ie make 104 the default) because other users need the box to be blank.
find out how a developer would change the original vba code, not how a user can interact with an input box by using vba.
I want to create an input box that will allow you to input the column number it should start in... so instead of it being fixed at A, you can choose column B, C, D all the way to the end if you like.
Sub SplitSheetsNoDelete1() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim Lmainsheet As String Dim Lrow As Integer Dim lcontinue As Boolean
Dim LColamaster As String Dim lcolatest As String
'Retrieve name of sheet that contains the data Lmainsheet = ActiveSheet.Name
I want to have 1 check box affect 3 others in the following way: check box 1 if checked, allow check/uncheck of check boxes 2, 3, 4 if unchecked, uncheck boxes 2, 3, 4 and do not allow checking check box 1 is linked to D1 which starts with a value of false. cell E1 is if condition to have value 1 when D1 has value of true.
I am creating a userform with 10 checkboxes. The first 9 checkboxes are user options. I want the 10th check box to be a "Select All" option i.e. if the 10th checkbox is checked all the other 9 options are deemed to have been selected.
The way I want the display to work is that if the 10th checkbox is ticked all other checkboxes are cleared. Also if the 10th checkbox is ticked and any of the other check boxes is selected then the 10th checkbox should be selected.
I have tried coding this up but the checkboxes don't seem to operate as desired. I placed some code on the click event for the last option button to set the vlaue for all other buttons to false. This works but the 10th check box doesn't get ticked itself. When I try to code up the other bit I get similar issues.
I am trying to to get it to work so that if there is a comment already in the cell then it would skip to the next macro. Here is what I am trying:
'Mgr Voids If Range("F9").Comment.Text = True Then End ElseIf Range("F9").Value > 50 Then MyInput = Application.InputBox("You Must Give A Reason For The Amount Of Mgr Voids For " & Range("F6")) ElseIf MyInput = "" Then End ElseIf MyInput = False Then End ActiveSheet. Unprotect ("13792468") ActiveSheet.Range("F9").AddComment Range("F9").Comment.Visible = False Range("F9").Comment.Text Text:="" & Chr(10) & (MyInput) & Chr(10) & "" ActiveSheet.Protect ("13792468") End If
The first part (If Range("F9").Comment.Text = True Then End) obviously doesn't work because I have no clue how it supposed to be... I was just hoping to get lucky. Any help would be appreciated.
I am building a tool that uses Pivot Tables (so I can't use track changes) and I have found cool code that will insert timestamp and username in the comment when a change occurs. But I need to modify the code to also take the old value and put it into the comment as well. Here is the code to register the change into the comment:
Is it possible to trigger a forced comment if a particular value is entered in a cell?
For instance, if the letter "F" is entered into a cell, I want the comment box is pop up, is this possible?
And lastly... is it possible to automatically group comments? for instance if a row has 6 comments, I'd like to combine all of them into a comment box on the same row.
my macro processes a lot of data and puts a comment in certain cells (several thousand of them). I would like to autosize the comments so that the text can all be read by default.
I'm using Excel 2002 and looking for a way to get rid of the red comment indicator in entirety. While I get that it's not possible there appears to be a workaround which I took off the Contextures website.
Sub RemoveIndicatorShapes()
Dim ws As Worksheet Dim shp As Shape
Set ws = ActiveSheet
For Each shp In ws.Shapes If Not shp.TopLeftCell.Comment Is Nothing Then If shp.AutoShapeType = _ msoShapeRightTriangle Then shp.Delete End If End If Next shp
I have some code that finds the appropriate string for a comment from another sheet. I then want to add the string to the appropriate range selection but am encountering an Object Variable or With Block Variable Not set on this section of
Range("C" & i).Select ActiveCell.Comment.Delete <== Here Or the Next line ActiveCell.AddComment (Commenta) ActiveCell.Comment.Shape.TextFrame.AutoSize = True
