How do I create a macro that makes my text change to subscript?

[Proj_Eng]

Hi Everyone,

I've attached a sample workbook which contains 4 worksheets with entries in a few cells in each sheet. I then used an If Statement to call the text "tmidspan" from sheet "Data2" when "8" Topped Hollow Core" is selected from the drop down menu on sheet "Input". This alone works fine, but I realize if I want to make the "midspan" portion of the text in cell D12 subscript I probably need to use VBA and create a macro to execute the command, but I have not been successful in figuring out how to make this work. Any help will be appreciated.

This is my first post, so if additional information is needed I will try to provide it.

[Logit]

.

Please Login or Register  to view this content.

[Logit]

.
Perhaps more useful would be a GENERIC macro. This allows you to simply make the
cell in question the ACTIVE CELL by clicking on it, then click the COMMAND BUTTON to activate the macro.

Please Login or Register  to view this content.

[Proj_Eng]

This worked. Thanks so much for the help. I may come back and ask additional questions as I try to modify it, but for now this works great.

[Proj_Eng]

I'm still having a slight problem. I incorporated the code you provided above into my function, but it's not working as I hoped it would. I only want tmidspan to appear when I select 8" Topped Hollow Core from the drop down menu on the Input sheet. I want the active cell to be empty when the other options are selected (I eventually want to extend this so that tmidspan appears when I pick any "Topped" hollow core product.) I've included the modified code below. How should it be changed?


Sub t_midspan()
'
' t_midspan Macro
' Makes text after the "t" subscript
'

'
ActiveCell.FormulaR1C1 = _
"=IF(Input!R[-21]C[-9]=Data1!R[-17]C[-10],Data2!R[6]C[-7],"""")"
With Range("D12")
Value = "tmidspan"
.Characters(Start:=2, Length:=7).Font.Subscript = True
End With

End Sub

[Logit]

.
Your macro utilizes this line :

Please Login or Register  to view this content.

The restricts the macro to work only for Cell D12 and no others.

If you use the macro in my Post #3 it will work for any ACTIVE CELL. In other words .. any cell that you click on.


If you are only interested in utilizing the term "tmidspan" and no other terms, the macro can be edited to accommodate
your needs. Otherwise, the macro as is can be used with any term.


???

[Proj_Eng]

The code as you provided works great! The problem is that "tmidspan" is showing in the active cell regardless of which item I choose from the drop down menu. I only want it displayed when specific items from my drop down list are selected, not at all times as it's currently doing. How do I modify it to work in the manner I'm describing? Let me know if you are not following my question and I will rephrase it. I appreciate all your help thus far.

[Proj_Eng]

Logit,

I revised the code as follows:

Please Login or Register  to view this content.

It's working perfectly, except that all characters in "tmidspan" are subscript. How do I modify this code so that only characters 2 through 7 are subscript?

[Logit]

.
Do you only want the Cell D5 to change per the macro ? 'Cuz that is the way the macro (your last post) is written.

Another question .. is D5 a drop down or just a normal cell ?

[Proj_Eng]

D5 is a normal cell. When I select text from a drop down menu on another sheet I want it to place "tmidspan" in D5 with midspan in subscript, depending on the item selected in the list. If the item selected doesn't require "tmidspan" then I want D5 to be empty. Hope this helps.

[Logit]

.
This macro from my post #2 will accomplish that, with a small edit .. changing the Cell named in the macro .

Please Login or Register  to view this content.

Post the macro in a regular module, just as you did with your original macro and connect a Command Button to it.
When you want "tmidspan" entered into D5, click the button.

Is that doable ?

[Proj_Eng]

I will try this when I get back to my office on Monday. I'm curious is there's a way to do it automatically when I choose something from my drop down list? In otherwords, can the macro be written so that when I pick specific items from my drop down list cell D5 automatically populates with my "tmidspan"? If I hadn't mentioned previously, the drop down list is on a separate sheet from the cell containing D5.

[Logit]

"is there's a way to do it automatically when I choose something from my drop down list?"

Yes. Post a copy of your workbook with minimal data included. Do not include any confidential information in the workbook.

[Proj_Eng]

Okay, I posted a copy of the work book. It's very generic. On sheet 1 is the drop down list (the list is drawn from data on sheet 2). Sheet three has the "tmidspan" text which I want to automatically populate to sheet 4 when I choose 8, 12 or 12.5 "Topped" hollow core panels from sheet 1. Let me know if you need additional information.

[Logit]

.

Please Login or Register  to view this content.

[Proj_Eng]

The only problem is that "tmidspan" is showing up on sheet 4 cell D5 regardless of what value is chosen from the drop down list on sheet 1. How do I modify it so that it won't show up when the other values are chosen from the list?

[Logit]

Are you running 64 bit or 32 bit operation system ?

[Logit]

Just in case you are running 32 bit ... try this version :

Attached

[Proj_Eng]

Yes, my system here is a 32-bit. This second version worked perfect. I will check my system at work and if it's a 64-bit then I will use the other version. Thank you for all your help!

[Logit]

.
Great !

You are welcome.

[Proj_Eng]

Logit,

I am still having an issue with the code on this. When I ran the spreadsheet and code you provided it runs perfectly, but when I incorporate it into the workbook I'm trying to develop it's not working correctly. I've been attempting to troubleshoot this all morning with no success. I've attached a sample spreadsheet, which is the spreadsheet you provided, slightly modified. This is the workbook titled Sample. This one works perfectly. This is how I want it to work in my workbook. However, when I insert the same code into my workbook (I've attached a copy with confidential information removed - it's titled Sample2)it's not working as it should. There are two things happening that I can't figure out.
1. I have to run the macro each time I want it to function. It doesn't automatically run after initializing it the first time.
2. Once the macro is run I can't then change my selection from the drop down list and have sheet "Print2" automatically modified to either add or remove "tmidspan". I have to re-run the macro to have it populate.

Any idea what is going on here?

[Logit]

.
Go into the VBE where your macro is located.

Double-click on Sheet2(Input)

In the code window to the right paste the following :

Please Login or Register  to view this content.

The above macro automatically checks if there is a change in Cell A1 (your drop down selection). When a change is observed, it
then runs the macro MakSubScript to determine if any action is required.


I downloaded your workbook SAMPLE2 and I'm experiencing an error when trying to create the drop down in Cell B1. There is a
drop down present already but it is frozen ... it does not function. When I attempt to create a drop down there I'm receiving
an error message. Are you receiving any error messages there ?

[Proj_Eng]

I was not receiving any error messages, that's what was so confusing. Your code above solved the issue!! Thanks again.

[Logit]

.
Just to be certain you have a working WORKBOOK, I rebuilt your project and attached it here.

[6StringJazzer]

Logit,

I revised the code as follows:

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer