Free Microsoft Excel 2013 Quick Reference

vba hyperlink for variable cell and variable worksheet

Hi

I am new to this forum so i hope i am communicating the right thing in order to get help.

So i have an excel file containing a complex plan. This file has an overview sheet and project plans.
In the overview sheet :column A contains a hyperlinks to other worksheets (1 project plan=1 worksheet) column B and C contain status and deadlines (information is taken from the worksheets).

Then i have a small macro that automatically adds an empty plan template and prompts the user to name it.

my challenge is to continue the code with the following
1. make the user select a cell/row in the overview sheet (title of the project has more characters than worksheet) and
2. hyperlink automatically the new project plan (which is a variable) to it and
3. pick up the necessary information for column B and C in the overview sheet from the new project plan (cells 5D and 5E)

Any help would be appreciated. Thank you so much!

!!! I also posted the same question on two ther forums.
Here are the links:
http://www.mrexcel.com/forum/showthread.php?t=541900
http://www.excelforum.com/excel-work...worksheet.html


How do I set up a VBA event for a CELL in a worksheet?

I want to be able to detect if the value of a cell changes, and have code respond accordingly.

Are there OnChange and OnEnter events similar to Access?

TIA,

Far

Hey Everyone,

I'd like to add new members to every cell and every worksheet in my
workbook. These members would be two different Booleans for the cells,
and an integer (for counting) for the worksheet. Is there an easy way
to do this?

Right now I'm trying to use existing members that aren't used much to
flag certain things, but this will eventually cause problems. And I
can't find anything in the Worksheet class that could be used as a
counter. Is there a wa to just add in come variables? Thanks!

-Jay

I often use:

For Each Cell in Worksheet.UsedRange

My understanding is that you cannot alter the UsedRange
property as it is read only. Is it possible to define a
range up front and use this in conjuction with a For Each
Loop using Cell and Worksheet. Best case scenario my goal
would be to either use this range to limit the area
covered by the For Each loop. Or worse case scenario use
it to limit the actions undertakeh within the loop

Dim NewRange as Range
For Each Cell in Worksheet.Something????
Next

Or

Dim NewRange as Range
'Note NewRange would be a subst of .UsedRange

For Each Cell in Worksheet.UsedRange
If Cell.Address is within NewRange Then
do something
Next

Hello to everyone i am trying to program a button in excel so that it takes the data from a few cells and it copies them in a new file. What i would like to do is also to make excel verify that if there is already data written in the new cells to write the new ones in the row below. Can somebody help me pls?
It's very important for my job.

hi can i have the code for remove of hyperlink for all cell in sheet1

thank

Hi

I am new to this forum so i hope i am communicating the right thing in order to get help.

So i have an excel file containing a complex plan. This file has an overview sheet and project plans.
In the overview sheet :column A contains a hyperlinks to other worksheets (1 project plan=1 worksheet) column B and C contain status and deadlines (information is taken from the worksheets).

Then i have a small macro that automatically adds an empty plan template and prompts the user to name it.

my challenge is to continue the code with the following
1. make the user select a cell/row in the overview sheet (title of the project has more characters than worksheet) and
2. hyperlink automatically the new project plan (which is a variable) to it and
3. pick up the necessary information for column B and C in the overview sheet from the new project plan (cells 5D and 5E)

Any help would be appreciated. Thank you so much!

Hi guys,

I have a document that I have alot of cells that has conditional formatting and turns the cell to the 80% grey color if the condition is true. I have that with no problem. What I want to do now is also add the work NA in the box in White Letters. Conditional formating doesnt allow me to do this, I can write a formula using this

=IF(OR(ISNUMBER(SEARCH({"Servo Spot Robot","MH Robot","Crimp Robot","Twin Servo Spot Robot","Stud Robot","Arc Robot","Snake","Spot/MH Robot","MH/Ped Welder"},$L5))),"NA"," ")

Then having to format the txt white manually and while that would work for my use, Its not suitable for users. I wont be able to protect the worksheet because they will not be able to edit the cell with their info if I do.

If someone has the time, or if it can even be done, Id like to make a button and assign a macro to it that when I click on the button, it will search for any cell that is 80% grey and write the word NA in it. My range of cells is N5 - CK249. If it can be automatically done say if anything in column L which are all drop down cells with the same lists in them is changed it auto runs the macro that would be awesome.

Ive searched for days and my having no knowledge of VBA has hindered me. Id appreciate any help. Thanks.

Hey all, I'm new here and just starting to explore the joys of Excel. I'm trying to learn a little bit about how to automate my monotonous copy-paste data entry job. I'm working with a workbook full of several dozen worksheets, which each have a table full of elements. Unfortunately, the order of elements for each sheet is not consistent throughout the workbook. So, I'm trying to find a way to Find a particular term (in column A), and copy/paste the corresponding time (in column B) to a summary list in is separate sheet. Is the Find function even the right way to go about this, or would VLookup be more advantageous?

This is what I crudely threw together, after a bit of searching old posts and trying to modify it to my own needs. However, in running it, I get a runtime error. Thanks for any help!


	VB:
	
 MakeSummaryTable() 
     
    Dim ws As Worksheet 
     
    Application.ScreenUpdating = False 
     
    Sheets("Summary").Activate 
     
    For Each ws In Worksheets 
         
        If ws.Name  "Summary" Then 
            Cells.Find(What:="Prep at Truck", After:=ActiveCell, LookIn:=xlFormulas, _ 
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
            MatchCase:=False, SearchFormat:=False).Activate 
             
            ActiveCell.Offset(0, 1).Copy 
            ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0) 
        End If 
         
    Next ws 
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I want to program a custom function using VBA. The normal Excel function I would type in a worksheet cell is:

=INDIRECT(ADDRESS(ROW($M7),N$1627,1))

which would give the the result in the cell located at the cell address for the row and column numbers noted in cells M7 and N1627, respectively.

What would be the VBA code for a custom function to shorten this formula in order to derive the same result?

I would like to use the hyperlink formula to link to a range of cells in the same sheet.

This should be simple and works when I press ctrl+K and enter A1:A2 for the cell reference; but not with the formula.

=hyperlink(A1:A2,"Link to A1:A2")

ALSO, if there's a way to hyperlink from one cell to multiple non-contiguous ranges that would be amazing!!! Something like pressing a hyperlinked cell A1 and selecting C1:C3 and E1:E3.

Any help is much appreciated...

Thanks,
Dan

Hey all,
Is this the right way to set a variable cell row?
i is an integer
I get a run time error 'Method Cell of Object Table failed' on the oTbl.cell(i,4)...
If I comment out oTbl.cell(i,4)...and oTbl.cell(i,5)...I get the runtime error 'The requested member of the collection does not exist' for line oTbl.cell(i,3)...
If i hard code the cell address it works, but with the variable I get the runtime errors.

Code:
With oWord
                            'Populate IP, NIP, TarDt/Cmnts
                            '*****TO DO --> ONCE CONFIRMED, MAKE ROW '2' BE DYNAMIC COUNTER
                            Set oTbl = .ActiveDocument.Tables(TableCt)
                            oTbl.Cell(i, 3).Range.Text = LoopRng.Offset(0, 3).Value
                            
                            'MsgBox LoopRng.Address
                            'MsgBox LoopRng.Offset(0, 3).Value
                            
                            
                            oTbl.Cell(i, 4).Range.Text = LoopRng.Offset(0, 4).Value
                            oTbl.Cell(i, 5).Range.Text = LoopRng.Offset(0, 9).Value
                        
                            End With
                              
                    i = i + 1
                    Set LoopRng = LoopRng.Offset(1, 0)
            

Loop
Thanks...banging my head on this one...

Could anyone suggest how I might simplify the following. I have many comboboxes in a worksheet and I want it so that when someone is in the combobox and they press "enter" then you move to the next cell. Right now I make a vba code for each combobox and I know theres a simple way to cycle through them. Take a look at the code and let me know what you think.
Thanks!
Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   If KeyCode = 13 Or KeyCode = 9 Or KeyCode = 39 Then
       Range("C5").Activate
   End If
End Sub
Private Sub ComboBox2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Or KeyCode = 9 Or KeyCode = 39 Then
       Range("C6").Activate
   End If
End Sub
Private Sub ComboBox3_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 13 Or KeyCode = 9 Or KeyCode = 39 Then
       Range("C7").Activate
   End If
End Sub
Private Sub ComboBox4_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   If KeyCode = 13 Or KeyCode = 9 Or KeyCode = 39 Then
       Range("C8").Activate
   End If
End Sub
Private Sub ComboBox5_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
   If KeyCode = 13 Or KeyCode = 9 Or KeyCode = 39 Then
       Range("C9").Activate
   End If
End Sub
This goes on until ComboBox 41.

Hi,
I am one week old to VBA and really need help to finish a project at my work
How do i write a code using macros that automatically makes a dropdown menu for the selected cells?

The drop down will contain a menu with a "fixed" list for each cell (let's say A, B, C, D)

So if i have 500 cells in my worksheet, the macro will add 500 comboboxes (each with A, b, C, D) in 500 different rows, but all in one common comlumn, so the user can select a different or same option for each cell

And also need an idea of code to run some other code for each different option in the list.
Thanks

I currently have a spreadsheet with mutliple worksheets. We noticed that the formatting for all cells changed to TIME format even though the cells might have been DATE, NUMERIC, or just GENERAL. Our user says they did not change the formatting. Wondering if any EXCEL 2007 does an automatic update that I am not aware of??? We are starting to use 2007 and not has familiar. Any thoughts are appreciated.

Hope someone can create a code that will:

1. after clicking a button located on the spreadsheet the code would check a list of cells to ensure none are empty. Example - part of the code would list cells to check (c1, d5, d6, g9), if any cell is left empty it would cause a warning message to appear that there is an empty cell and then the user would click ok and fill the cell. It is not necessary for the code to indicate which cell is left empty but the list of cells might change as need arises.

2. the code would then save the excel sheet as a pdf using the contents of cell c1 as the file name and save it to a specific directory automatically.

thanks for any assistance.

Hey,

I have encounter a problem in trying to populate cell and here is a sample file in which I would like some help in. First I got the document to group by the Type on column B and then would like to populate it on the top row of the excel spread sheet while doing subtotals for column E and F for each subgroup. Sorry for the bother, if possible look at the file and please help. In the document the Bolded section is not there and I would like it to turn out like that as a solution ...

I am trying to create a macro in order to request user input for three cells and then update all the selected sheets in a workbook with that input. So that cell B3 in all selected worksheets would be updated with the input received from enter date, etc.

Sub NewQuarter()

Range("B3").Value = InputBox("enter date")
Range("C3").Value = InputBox("enter Quarter")
Range("D3") = "Fiscal Year 2005"
Range("C53").Value = InputBox("enter # of periods")

End Sub

Dear experts,
I wrote a VBA code for my friend in Excel 2002, but it would stumble in his 2003. Instead of installing the version and digging into the macro, I was hoping there would be an easy workaround. Therefore, although I suspect that this problem may be a result of something else, I wanted to clarify for me the following:

Are there any differences between VBA codes for Excel 2002 and Excel 2003, and if true, are there general guidelines with respect to the differences ?
Does the service pack status matter for Excel 2002? In other words would the same macro work on Excel 2002 regardless of whether it was updated or not?Thanks a lot in advance.

I am looking for a way to autofit text in a merged cell (which is the result of a merging of cells as well as columns) by only adjusting the row height.
Example: suppose I have merged cell A1 to C5 and entered some text in it. Now I want a macro to autoadjust the height of the cells to fit the text in it.

Is something like that doable? The code should leave the columnwidth as it is and only change the heights of the rows of which the merged cell is made up.

I found some code autoadjusting the height for merged cells on one row, but that is not enough.
http://groups.google.com/group/micro...c6bca447bd8902

Any help would be greatly appreciated.

Hi experts,

I've received some help from Mr. jindon on the following mrexcel link:
http://www.mrexcel.com/forum/showthread.php?t=303154

I believe I'm almost there, but can't seem to get the code to do one thing.

I'm running multiple iterations and each time, range (B23:E93) changes values. I want to capture the first iteration value for each cell and add that value to the next iteration and keep adding all iterations together. The final part of the code pastes the final results elsewhere.

The current code is adding the final value multiple times, but does not add the value of the first runs.

Can someone suggest a way to fix the following code?

Code:
Sub Resources()
         
Dim i As Integer
Dim Resource_output As Worksheet
Dim b As Integer, bb As Integer, a()
Dim n As Integer
 
    For i = 3 To 4
        
    'other code
 
        Resource_output.Activate
 
        a = Range("B23:E93").Value
        n = i
    
            myValues = a
        
            For b = 1 To UBound(a, 1)
                For bb = 1 To UBound(a, 2)
                    myValues(b, bb) = a(b, bb) + myValues(b, bb)
                Next bb
            Next b
            
        If n > 4 Then
            Sheets("All Resources").Range("b2").Resize(UBound(myValues, 1), _  UBound(myValues, 2)).Value = myValues
            Exit Sub
        End If
        
        n = n + 1
        Next i
You guys are awesome! thanks!!

Hi;

I working on a loop that would find blank cell. These cells have a formula in excel, but if certain conditions are met, the cell is left blank. What property in VBA checks for blank cell? I don't thin it's the "empty" property??

Hi,

What is the Excel 2003 VBA code for a cell formula linked to another excel workbook ?

Active Workbook cell A1 ='C:directory[Prices.xls]Day25'!$A$15

thx
thb

I have an add-in, which provides certain user defined functions to xl.

However, an argument of some of these functions is a text string
representing the name of a defined range in the "calling"
workbook/worksheet", which does not get resolved correctly on recalcs of
in-active workbooks/sheets. For this reason, it would be useful to be able
to get information (in vba) on from which workbook and worksheet a function
call is beeing made. Is this possible and if so how??? Bwetter programming
(passing the range) could avoid this problem, but we're stuck with what we
have a need the fix to be backwards compatible...

Hi,

I have just noticed a slight issue with some of my code, basically it emails down a list, but not everyone on the list has an email address. If this happens it doesn't send any emails to the individual or the people after that.

I need to do get a "for each cell in range if cell.value <> "" then ... next cell else next cell" in there, but it wants another for.

This is my code that doesn't work, I also wonder is there a way to get it so that if someone is on the list twice they will only get one email?

 Sub EMAILLIST()

    Dim cell As Object
    Dim NR As Long
    Dim tagerror As String
    Dim Email_Send_To, Email_Send_From, Email_Subject, Email_Body As String
    Dim strUserEmail As String
    Dim strFirstClassPassword As String
    Dim errPar As String
    Dim iMsg As Object
    Dim iConfig As Object
    Dim sConfig As Variant
    Dim Row As Integer
       
    
        
    strUserEmail = "test@email.ac.uk"
    strFirstClassPassword = "password"

    Set iMsg = CreateObject("CDO.Message")
    Set iConfig = CreateObject("CDO.Configuration")
    iConfig.Load -1
    Set sConfig = iConfig.Fields
        
        With sConfig
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "192.168.0.5" 'Name or
IP of remote SMTP server
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25  'Server Port
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = strUserEmail
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = strFirstClassPassword
            .Update
        End With
     
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
        
    With Worksheets("OUTPUT")
    NR = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
        For Each cell In Worksheets("OUTPUT").Range("I2:I" & NR)
        
        If cell.Value <> "" Then
 '-----------------------------------------------------------------------------

    Email_Send_To = cell.Value
    Email_Send_From = "test@email.ac.uk"
    Email_Subject = "SELF CERTIFICATION FORM URGENT RESPONSE REQUIRED " & Format(Now, "mm/yyyy")
    Email_Body = "Dear " & Range("B" & cell.Row) & "," & vbNewLine &
vbNewLine _
                 & "We have received notification from your manager that you have been sick/absent, you are required
to " _
                 & "complete a self certification form. This is mandatory and failure to complete this form may
result in " _
                 & "you not receiving pay for the period in which you were absent. Once you have completed the form
you " _
                 & "should return it to your manager for your return to work interview. It is your responsibility to
make " _
                 & "sure you manager performs a return to work interview, within 7 days of you returning to
work." _
                 & vbNewLine & vbNewLine _
                 & "Please follow this link to the self certification form: " _
                 & "http://link/file.pdf" _
                 & vbNewLine & vbNewLine _
                 & "If you have any question please feel free to contact HR " _
                 & vbNewLine & vbNewLine _
                 & "Kind Regards," _
                 & vbNewLine & vbNewLine _
                 & "HR"


'-----------------------------------------------------------------------------
      

    

    With iMsg
        Set .Configuration = iConfig
    End With
    
            iMsg.To = Email_Send_To
            iMsg.From = Email_Send_From
            iMsg.Subject = Email_Subject
            iMsg.Textbody = Email_Body
            iMsg.Send

          On Error GoTo tagerror

    Next cell
    
    Else
    
    Next cell
    
If ActiveSheet.Range("a1") = "" Then
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True

    
Else
    Exit Sub
End If

clean_up:
    With Application
       .EnableEvents = True
       .ScreenUpdating = True
    End With
    Exit Sub
    
tagerror:
    MsgBox "Error: (" & Err.Number & ") " & Err.Description & " at " &
Err.Source, vbCritical
    Resume clean_up
    
End Sub